What's the last day of the month?

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners
edited February 28 in Vertica Tips

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!

Sign In or Register to comment.