How do I find the next Monday/Tuesday/etc.. with SQL?

Being able to calculate the date of the coming Thursday may come really handy if your sales revenue follows a very strong weekday pattern as it enables you to use weekdays, rather than fixed dates, as a basis for making your weekly financial projections.

How do I do this in practice?

Vertica offers a very easy way out – the next_day(timestamp, weekday) function

Achieving the same result in SQL Server, MySQL and PostgreSQL requires a bit more effort.

Whereas the logic of the calculation in the three databases is essentially the same, the syntax will be quite different.

This is due to the differences in syntax in:

The differences in addition of a time interval to a date timestamp are particularly visible in PostgreSQL, where due to the dynamic nature of the interval being calculated, one must also make sure that the said interval is also casted as such by either using:

  • timestamp + cast((num_of_days || ‘ day’) as interval)
  • timestamp + “interval” (num_of_days || ‘ days’)

P.S. Note that unlike PostgreSQL which assigns 1 to 5 for weekdays, SQL Server and MySQL assign 2 to 6

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.