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.

asked 13 May, 13:22

Bud%20Durland%20MRP's gravatar image

Bud Durland MRP
26651021
accept rate: 33%


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 :)

permanent link

answered 14 May, 08:37

Breck%20Carter's gravatar image

Breck Carter
31.9k5177061035
accept rate: 20%

edited 14 May, 08:53

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

(14 May, 09:39) Volker Barth
Replies hidden
1

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 :)

(14 May, 15:36) Breck Carter

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...

(14 May, 16:18) Volker Barth
2

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 :)

(15 May, 07:00) Breck Carter
1

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

(17 May, 08:23) Bud Durland MRP
Comment Text Removed
Comment Text Removed
2

I love such solutions!! Thanks Breck

(17 May, 11:09) Baron
showing 2 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20
×20

question asked: 13 May, 13:22

question was seen: 177 times

last updated: 17 May, 11:09