LAST_DAY

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

Read syntax diagram
>>-LAST_DAY(expression)----------------------------------------><

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. Start of changeA time zone in a string representation of a timestamp is ignored.End of change For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

Start of changeIf 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.End of change

Start of changeThe 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.End of change

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.

Start of changeAny hours, minutes, seconds, or fractional seconds information that is included in expression is not changed by the function.End of change

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'.
Start of changeExample 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).End of change
Start of changeExample 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.End of change
Start of changeExample 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;
End of change