What's the last day of the month?

[Deleted User]

This tip was authored by Jim Knicely.

The Vertica built-in LAST_DAY function returns the last day of the month for a specified date. This function comes in handy for leap years.

Example:

dbadmin=> SELECT last_day('02/28/2018') NOT_A_LEAP_YEAR, 
                 last_day('02/28/2020') A_LEAP_YEAR;
 NOT_A_LEAP_YEAR | A_LEAP_YEAR
-----------------+-------------
 2018-02-28      | 2020-02-29
(1 row)

In one of my previous positions, an accountant asked how to compute the last day of the previous month. That’s not a problem when you combine a few Vertica built-in functions.

Example:

dbadmin=> SELECT LAST_DAY(ADD_MONTHS('03/28/2020', -1)) STILL_A_LEAP_YEAR;
 STILL_A_LEAP_YEAR
-------------------
 2020-02-29
(1 row)

Have Fun!