LAST_DAY

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

LAST_DAY(date-expression)

date-expression

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

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

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

A date-expression timestamp is data type %Library.TimeStamp (yyyy-mm-dd hh:mm:ss.fff).

The time portion of a %TimeStamp string is optional. The date-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.

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.

This function can also be invoked from ObjectScript using the LASTDAY() method call:

  WRITE $SYSTEM.SQL.LASTDAY("2004-02-25"),!
  WRITE $SYSTEM.SQL.LASTDAY(59590)

The following examples return the last day of the month as a Caché 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 Caché 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