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!

Happy SQL-ing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.