4

In learning how to get the date of a day of the week from the previous week (e.g. the date of Monday from the previous week), I found the following two expressions coming up commonly, where you change the very last number (e.g. 0=Monday) depending on which day you want:

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

SELECT DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)

Both seem to return the correct Date 2017-05-29 when I run it today (2017-06-17).

My question is - how does this work? It seems to be getting a count of weeks since 6 or 7 weeks after 1900-01-01? And why does it work with both a 6 and 7?

Also, are there edge cases one would need to be aware of where it might not give the expected results?

| improve this question | |
9

This takes advantage of the fact that 1900-01-01 is a Monday. So adding n number of weeks will also be a Monday.

Here are the datetime values that are being represented by the integers in that expression:

dateadd(week, datediff(week, 7 /*'19000108'*/, getdate()), 0 /*'19000101'*/)

Adding the number of weeks since 1900-01-08 (the week after the first Monday) to 1900-01-01 returns one week less than the current week, i.e. Monday of last week.

These three are equivalent: 

select dateadd(week, datediff(week, 7 , getdate()), 0)
select dateadd(week, datediff(week, '19000108', getdate()), '19000101')
select dateadd(week, datediff(week, 0, getdate())-1, 0)

rextester demo: http://rextester.com/BQX59903

| improve this answer | |
  • This all makes sense except 1 thing... the 6 and the 7 - why would one add either 6 or 7 weeks from the first monday? I suspect they used 6/7 thinking aout "days", but it's not days, its wk right? – GWR Jun 7 '17 at 13:22
  • 1
    @GWR 6 or 7 are 1900-01-07 and 1900-01-08 respectively, not weeks. The likely cause of someone using 7 would be if their native calendar week starts on a Monday, vs US where it starts on Sunday. The reason 6 works even if someone has set datefirst 1 (to set the first day of the week as Monday instead of Sunday) explicitly or implicitly by language setting, datediff(week...) is going to be performed as if set datefirst 7 was in place. rextester demo: rextester.com/YWJRZ24690 – SqlZim Jun 7 '17 at 13:32
  • I get it... I was mixing it up... the 6 and 7 I was thinking of as count of days or weeks, but its a value that resolves into a date expression... Thanks for the assist. – GWR Jun 7 '17 at 13:41
  • @GWR Happy to help! – SqlZim Jun 7 '17 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.