- Need to provide a daily estimation of how many clicks you’ll make by the end of next month?
- Your client expects you to deliver a monthly report on their performance?
That’s when knowing how to get the last day of the current, previous or next month might come in handy
Whereas Vertica and MySQL offer the simplest syntax for figuring the last day of the current month through their built-in function last_day (timestamp), they do expect you to figure out the addition/subtraction of months yourself when getting the last day of the previous or next month.
SQL Server is a bit simpler in that respect – the syntax of eomonth(timestamp, period) by default requires the addition/subtraction of a period
PostgreSQL, on the other hand, requires quite a bit of creativity on your part, as it does not support a direct equivalent of either of these fucntions.
Yet, there’s no reason for fretting!
Remember date_trunc()?
With a bit of extra subtraction from its result, you’re all set!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
———————————-SQL Server————————————————————————- | |
select | |
convert(date,getdate()) as Today, | |
eomonth(getdate(),–1) as LastDayPreviousMonth, | |
eomonth(getdate(),+0) as LastDayThisMonth, | |
eomonth(getdate(),+1) as LastDayNextMonth | |
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth | | |
————|———————|—————–|—————–| | |
2017–11–18 |2017–10–31 |2017–11–30 |2017–12–31 | | |
———————————-Vertica—————————————————————————- | |
select | |
now() as Today | |
last_day(Now()) as LastDayofCurrentMonth, | |
last_day(add_months(Now(),–1)) as LastDayofPreviousMonth, | |
last_day(add_months(Now(), 1)) as LastDayofNextMonth | |
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth | | |
————|———————|—————–|—————–| | |
2017–11–18 |2017–10–31 |2017–11–30 |2017–12–31 | | |
#——————————–MySQL——————————————————————————- | |
select | |
date(now()) as Today, | |
last_day(adddate(Now(), –31)) as LastDayPreviousMonth, | |
last_day(Now()) as LastDayThisMonth, | |
last_day(adddate(Now(), +31)) as LastDayNextMonth | |
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth | | |
————|———————|—————–|—————–| | |
2017–11–18 |2017–10–31 |2017–11–30 |2017–12–31 | | |
———————————-PostgreSQL—————————————————————————- | |
select | |
Date(Now()) as Today, | |
Date(date_trunc('month', Now() + interval '0 month') – interval '1 day') as LastDayPreviousMonth, | |
Date(date_trunc('month', Now() + interval '1 month') – interval '1 day') as LastDayThisMonth, | |
Date(date_trunc('month', Now() + interval '2 month') – interval '1 day') as LastDayNextMonth | |
today |lastdaypreviousmonth |lastdaythismonth |lastdaynextmonth | | |
————|———————|—————–|—————–| | |
2017–11–18 |2017–10–31 |2017–11–30 |2017–12–31 | | |
Happy SQL-ing!