How do I find the last day of the previous/next month with SQL?

• 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!

	———————————-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 |

view raw

SQL_find_the_last_day_of_a_month.sql

hosted with ❤ by GitHub

Happy SQL-ing!