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