LAST_DAY scalar function

The LAST_DAY scalar function returns a date that represents the last day of the month of the date argument.

The schema is SYSIBM.

expression

An expression that specifies the starting date. The expression must returns a value of one of the following data types:

a date
a timestamp
a valid string representation of a date or timestamp

An argument with a character string data type must not be a CLOB. An argument with a graphic string data type must not be a DBCLOB. A string argument must have an actual length that is not greater than 255 bytes. A time zone in a string representation of a timestamp is ignored. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

If expression is a TIMESTAMP WITH TIME ZONE value, expression is first cast to a TIMESTAMP WITHOUT TIME ZONE value with the same precision as expression.

The result of the function has the same data type as expression, unless expression is a string, in which case the result data type is DATE.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

The result can be null; if the argument is null, the result is the null value.

Any hours, minutes, seconds, or fractional seconds information that is included in expression is not changed by the function.

Example 1: Set the host variable END_OF_MONTH with the last day of the current month.

   SET :END_OF_MONTH = LAST_DAY(CURRENT_DATE);

The host variable END_OF_MONTH is set with the value representing the end of the current month. If the current day is 2000-02-10, END_OF_MONTH is set to 2000-02-29.

Example 2: Set the host variable END_OF_MONTH with the last day of the month in EUR format for the given date.

   SET :END_OF_MONTH = CHAR(LAST_DAY('1965-07-07'), EUR);

The host variable END_OF_MONTH is set with the value '31.07.1965'.

Example 3: Assume that host variable PRSTSZ contains '2008-02-29.20.00.000000 -08.30'. The TIMESTAMP WITH TIME ZONE value is implicitly cast to TIMESTAMP WITHOUT TIME ZONE before the LAST_DAY function is evaluated.

SELECT LAST_DAY(:PRSTSZ) 
		FROM PROJECT;

The LAST_DAY function returns the value '31' (month in UTC is March).

Example 4: Assume PRSTSZ is a host variable with the string value '2008-04-15.20.00.000000-08.30'. The string value, which is a string representation of a timestamp with a time zone, is implicitly cast to a DATE before the LAST_DAY function is evaluated. The LAST_DAY function returns the last day of the month as a DATE value.

SELECT LAST_DAY(:PRSTSZ) 
	FROM PROJECT;

The LAST_DAY function returns the value ‘2008-04-30', the last day of the month of April, as a DATE value.

Example 5: Assuming that the default date format is ISO, the following select statement returns '2000–04–30', which is the last day of April in 2000:

SELECT LAST_DAY('2000-04-24')
	FROM SYSIBM.SYSDUMMY1;