How can I find the last day of last month in SQL?

[Jbaskin]

How can I find the last day of last month using SQL in Vertica? For instance, last month was January and the last day was the 31st. I'd like to get back 31. And for next month, the answer would be 29. Thanks.

[JimKnicely]

Although there isn't a built in function for that, it's easy enough to get the result you need via a combination of built in functions.

For instance in vSQL:

Code: Select all

vertica01=> SELECT CURRENT_DATE() "TODAY", DAY(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1))) "LAST DAY OF LAST MONTH";
   TODAY    | LAST DAY OF LAST MONTH
------------+------------------------
 2012-02-09 |                     31

[JeffSatler]

Wow, I needed this, thanks!