Skip to main content

LAST_DAY (SQL)

A date function that returns the date of the last day of the month for a date expression.

Synopsis

LAST_DAY(date-expression)

Description

LAST_DAY returns the date of the last day of the specified month as an integer in $HOROLOG format. Leap year differences are calculated, including century day adjustments: 2000 is a leap year, 1900 and 2100 are not leap years.

The date-expression can be an InterSystems IRIS date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.

A date-expression timestamp can be either data type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).

The time portion of a %TimeStamp string is optional.

LAST_DAY returns 0 (in Display mode 12/31/1840) when an invalid date is specified: the day or month as zero; the month greater than 12; or the day larger than the number of days in that month on that year. The year must be in the range 0001 through 9999.

This function can also be invoked from ObjectScript using the LASTDAY()Opens in a new tab method call:

  WRITE $SYSTEM.SQL.Functions.LASTDAY("2018-02-22"),!
  WRITE $SYSTEM.SQL.Functions.LASTDAY(64701)

Arguments

date-expression

An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.

Examples

The following examples return the last day of the month as an InterSystems IRIS date integer. Whether this value is displayed as an integer or as a date string depends on the current SQL Display Mode setting.

The following two examples both return the number 59594 (which corresponds to '2004–02–29') because the last day of the month on the specified date is February 29 (2004 is a leap year):

SELECT LAST_DAY('2004-02-25')
SELECT LAST_DAY(59590)

The following examples all return the InterSystems IRIS date integer corresponding to the last day of the current month:

SELECT LAST_DAY({fn NOW()}) AS LD_Now,
       LAST_DAY(CURRENT_DATE) AS LD_CurrDate,
       LAST_DAY(CURRENT_TIMESTAMP) AS LD_CurrTstamp,
       LAST_DAY($ZTIMESTAMP) AS LD_ZTstamp,
       LAST_DAY($HOROLOG) AS LD_Horolog

See Also

FeedbackOpens in a new tab