LAST_DAY Function

The LAST_DAY function requires a DATE or DATETIME expression as its only argument. It returns the date of the last day of the month that its argument specifies.

The data type of this returned value is the same data type as the argument. The difference between the returned value and the argument is the number of days remaining in that month.

The following query returns the DATE representation of the current date, the date of the last day in the current month, and the integer number of days (calculated by subtracting the first DATE value from second) before the last day in the current month:

SELECT TODAY AS today, LAST_DAY(TODAY) AS last,
   LAST_DAY(TODAY) - TODAY AS days_left 
   FROM systables WHERE tabid = 1;

If the query were issued on 12 March 2018, with MDY4/ as the DBDATE setting for the default locale, it would return the following information:

today      last         days_left

03/12/2018 03/31/2018          19

In the SELECT statement of this example, there is no name conflict in the Projection clause between the TODAY operator and the identifier today, because the AS keyword indicates to HCL OneDB™ that today is a display label.

If you use a host variable to store the argument to LAST_DAY, but the data type of the argument is not known at prepare time, HCL OneDB assumes that the data type is DATETIME YEAR TO FRACTION(5). If at runtime, after the statement has been prepared, the user supplies a DATE value for the host variable, error -9750 is issued. To prevent this error, specify the data type of the host variable by using a cast, as in this program fragment:

sprintf(query, “, 
   “select last_day(?::date) from mytab”);
EXEC SQL prepare selectq from :query;
EXEC SQL declare select_cursor cursor for selectq;
EXEC SQL open select_cursor using :hostvar_date_input;
	
EXEC SQL fetch select_cursor into :var_date_output;