How do I find the date that is a number of months from/before now with SQL?

Did you ever have to check what the status of an indicator was exactly:

  • 2 years ago
  • 6 months ago
  • 3 weeks ago

What is crucial in each one of those instances is the ability to calculate what the exact date at the time was, so that you’re retrieving data for corresponding periods.

It can be a bit confusing to arrive at these calculations as the different databases could have different function names, different function syntax or even no functions at all.

Let’s go through some of the most popular databases out there:

1. SQL Server: dateadd (period, value, timestamp)
2. MySQL: date_add (timestamp, ‘interval’ value, period)*
3. Vertica: timestampadd (period, value, timestamp)
4. PostgreSQL: timestamp – INTERVAL ‘period’

I know… These probably don’t make much sense – at least they looked terribly confusing when I saw them in the databases official documentation.

So let’s check out the actual SQL:

Good news is that, as you’ll have seen above, there isn’t much difference if you’d like to move backwards or forwards.

All you need to do is just change the value sign, which is great because those functions can then be used if you’d like to make some future extrapolations as well.

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.