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:

• adding a time period to a date
• extracting part of a date from a timestamp

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

view raw SQL_next_day_function_syntax.sql hosted with ❤ by GitHub

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

Happy SQL-ing!