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’)
----------------------------Vertica----------------------------------------------------------------------- | |
select | |
Now() as Today, | |
next_day(Now(), 'Monday') as NextMonday, --returns the date of next Monday | |
next_day(Now(), 'Tuesday') as NextTuesday, --returns the date of next Tuesday | |
next_day(Now(), 'Wednesday') as NextWednesday, --returns the date of next Wednesday | |
next_day(Now(), 'Thursday') as NextThursday, --returns the date of next Thursday | |
next_day(Now(), 'Friday') as NextFriday, --returns the date of next Friday | |
next_day(Now(), 'Saturday') as NextSaturday, --returns the date of next Saturday | |
next_day(Now(), 'Sunday') as NextSunday --returns the date of next Sunday | |
Today |NextMonday |NextTuesday |NextWednesday |NextThursday |NextFriday |NextSaturday |NextSunday | | |
-----------|-----------|------------|--------------|-------------|-----------|-------------|-----------| | |
2017-11-23 |2017-11-27 |2017-11-28 |2017-11-29 |2017-11-30 |2017-11-24 |2017-11-25 |2017-11-26 | | |
---------------------------SQL Server--------------------------------------------------------------------- | |
select | |
convert(date,getdate()) as Today, | |
-- 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Sturday | |
convert(date, dateadd(day, 2, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 2 then 0 else 7 end )) as NextMonday, | |
convert(date, dateadd(day, 3, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 3 then 0 else 7 end )) as NextTuesday, | |
convert(date, dateadd(day, 4, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 4 then 0 else 7 end )) as NextWednesday, | |
convert(date, dateadd(day, 5, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 5 then 0 else 7 end )) as NextThursday, | |
convert(date, dateadd(day, 6, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 6 then 0 else 7 end )) as NextFriday, | |
convert(date, dateadd(day, 7, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 7 then 0 else 7 end )) as NextSaturday, | |
convert(date, dateadd(day, 1, getdate() - datepart(dw, getdate()) | |
+ case when datepart(dw, getdate()) < 1 then 0 else 7 end )) as NextSunday | |
Today |NextMonday |NextTuesday |NextWednesday |NextThursday |NextFriday |NextSaturday |NextSunday | | |
-----------|-----------|------------|--------------|-------------|-----------|-------------|-----------| | |
2017-11-23 |2017-11-27 |2017-11-28 |2017-11-29 |2017-11-30 |2017-11-24 |2017-11-25 |2017-11-26 | | |
----------------------------MySQL-------------------------------------------------------------------------- | |
select | |
date(Now()) as Today, | |
-- 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Sturday | |
date(adddate(Now(), 2 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 2 then 0 else 7 end )) as NextMonday, | |
date(adddate(Now(), 3 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 3 then 0 else 7 end )) as NextTuesday, | |
date(adddate(Now(), 4 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 4 then 0 else 7 end )) as NextWednesday, | |
date(adddate(Now(), 5 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 5 then 0 else 7 end )) as NextThursday, | |
date(adddate(Now(), 6 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 6 then 0 else 7 end )) as NextFriday, | |
date(adddate(Now(), 7 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 7 then 0 else 7 end )) as NextSaturday, | |
date(adddate(Now(), 1 - dayofweek(Now()) | |
+ case when dayofweek(Now()) < 1 then 0 else 7 end )) as NextSunday | |
Today |NextMonday |NextTuesday |NextWednesday |NextThursday |NextFriday |NextSaturday |NextSunday | | |
-----------|-----------|------------|--------------|-------------|-----------|-------------|-----------| | |
2017-11-23 |2017-11-27 |2017-11-28 |2017-11-29 |2017-11-30 |2017-11-24 |2017-11-25 |2017-11-26 | | |
---------------------------------PostgreSQL-------------------------------------------------------------------- | |
select | |
-- 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday | |
--------------------------------------------------------------------------------------------------------------- | |
-- timestamp + cast((num_of_days || ' day') as interval) | |
---OR | |
-- timestamp + "interval" (num_of_days || ' days') | |
---------------------------------------------------------------------------------------------------------------- | |
Date(Now()) as Today, | |
Date(Now() + cast((1 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 1 then 0 else 7 end) || ' day' as interval)) as NextMonday, | |
Date(Now() + cast((2 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 2 then 0 else 7 end) || ' day' as interval)) as NextTuesday, | |
Date(Now() + cast((3 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 3 then 0 else 7 end) || ' day' as interval)) as NextWednesday, | |
Date(Now() + cast((4 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 4 then 0 else 7 end) || ' day' as interval)) as NextThursday, | |
Date(Now() + cast((5 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 5 then 0 else 7 end) || ' day' as interval)) as NextFriday, | |
Date(Now() + cast((6 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 6 then 0 else 7 end) || ' day' as interval)) as NextSaturday, | |
Date(Now() + cast((7 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 7 then 0 else 7 end) || ' day' as interval)) as NextSunday | |
today |nextmonday |nexttuesday |nextwednesday |nextthursday |nextfriday |nextsaturday |nextsunday | | |
-----------|-----------|------------|--------------|-------------|-----------|-------------|-----------| | |
2017-11-23 |2017-11-27 |2017-11-28 |2017-11-29 |2017-11-30 |2017-11-24 |2017-11-25 |2017-11-26 | | |
--------------------------------------OR------------------------------------------------------------------ | |
select | |
-- 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday | |
--------------------------------------------------------------------------------------------------------------- | |
-- timestamp + cast((num_of_days || ' day') as interval) | |
---OR | |
-- timestamp + "interval" (num_of_days || ' days') | |
---------------------------------------------------------------------------------------------------------------- | |
Date(Now()) as Today, | |
Date(Now() + "interval" ((1 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 1 then 0 else 7 end) || ' day')) as NextMonday, | |
Date(Now() + "interval" ((2 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 2 then 0 else 7 end) || ' day')) as NextTuesday, | |
Date(Now() + "interval" ((3 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 3 then 0 else 7 end) || ' day')) as NextWednesday, | |
Date(Now() + "interval" ((4 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 4 then 0 else 7 end) || ' day')) as NextThursday, | |
Date(Now() + "interval" ((5 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 5 then 0 else 7 end) || ' day')) as NextFriday, | |
Date(Now() + "interval" ((6 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 6 then 0 else 7 end) || ' day')) as NextSaturday, | |
Date(Now() + "interval" ((7 - extract(dow from Now())) | |
+ (case when extract(dow from Now()) < 7 then 0 else 7 end) || ' day')) as NextSunday | |
P.S. Note that unlike PostgreSQL which assigns 1 to 5 for weekdays, SQL Server and MySQL assign 2 to 6
Happy SQL-ing!