Getting the date of a specific weekday

[BudDurland]

I find myself needing to determine the date of, for example, the next Monday following today. I came up with the following code. While readable, it seems to me that there is probably a more elegant solution. I'm using WinSQL, so the '::DayID' is a run-time input parameter for the day of the week I want (1=Sunday, etc)

select 
case 
  when ::DayID > dow(Today()) then dateadd(day, (::DayID - dow(Today())), Today())
  when ::DayID = dow(Today()) then dateadd(week, 1, Today())
  else dateadd(day, (::DayID - dow(Today()) + 7), Today())
end

I suspect someone else has already crossed this bridge and has a better way. If not, I'll just define this as a function in the database and be happy.

[Breck_Carter · Accepted solution]

Wrapping it in a function is probably still a good idea.

This may not be "readable" but it is "set oriented".

It also mimics how I find the next Monday on a paper calendar by moving my finger across the days 🙂

-- next Monday following today

SELECT DATEADD ( DAY, sa_rowgenerator.row_num, CURRENT DATE ) AS "next Monday"
  FROM sa_rowgenerator ( 1, 7 )
 WHERE DOW ( "next Monday" ) = 2;

next Monday             
----------------------- 
2021-05-17 00:00:00.000 

I thought it needed a TOP 1 ORDER BY but it doesn't 🙂

[VolkerBarth · Accepted solution]

Aside: I'm not sure whether DOW is dependent of the first_day_of_week option, newest docs seem to imply, it is...

[Breck_Carter · Accepted solution]

Surely you mean to say "it is NOT dependent" since the V10 through V17 docs make that clear: "The DOW function is not affected by the value specified for the first_day_of_week database option. For example, even if first_day_of_week is set to Monday, the DOW function returns a 2 for Monday."

All docs since V5 give the same definition "DOW( date-expr ) Returns a number from 1 to 7 representing the day of the week of the given date, with Sunday=1, Monday=2, and so on."

The Watcom Rule implies that "day of week" would be independent of "first day of week" because the former is independent of locale... i.e., Monday does not become Sunday when you move between locales... except when you cross the date line 🙂

[VolkerBarth · Accepted solution]

Sorry, a misunderstanding on my part: Whereas the DOW() function ignores that option, the date part WEEKDAY does respect it, and I had misread your statement as using DATEADD with WEEKDAY/DW...(probably because I had tried that myself...).

---

That different handling is contrary to the Watcom Rule, methinks...

[Breck_Carter · Accepted solution]

Everything about locales is contrary to the Watcom Rule, as is much of life itself 🙂

Want proof? Change the Windows 10 - Control Panel - Region - Short date format and see how many command files and apps that breaks 🙂

[BudDurland · Accepted solution]

(Removing comment made before reading the rest of the thread)

[Baron · Accepted solution]

I love such solutions!! Thanks Breck