3

My company groups all tasks into individual weeks that end on a Thursday. Thus a task due on 3/20/19 would be grouped into the 3/21 week and tasks due on 3/22 group into the 3/28/19 week.

I'm looking to calculate this field (called duedate_Weekdue) based on an input duedate.

The following works but doesn't seem like the simplest way to do this. Anyone have more elegant methods?

Select 
getdate() as duedate,
datepart(yy,getdate()) as duedate_yr,
datepart(ww,getdate()) as duedate_ww,
CASE
    When datename(dw,Dateadd(day,1,getdate()))='Thursday' Then Dateadd(day,1,getdate())
    When datename(dw,Dateadd(day,2,getdate()))='Thursday' Then Dateadd(day,2,getdate())
    When datename(dw,Dateadd(day,3,getdate()))='Thursday' Then Dateadd(day,3,getdate())
    When datename(dw,Dateadd(day,4,getdate()))='Thursday' Then Dateadd(day,4,getdate())
    When datename(dw,Dateadd(day,5,getdate()))='Thursday' Then Dateadd(day,5,getdate())
    When datename(dw,Dateadd(day,6,getdate()))='Thursday' Then Dateadd(day,6,getdate())
    When datename(dw,Dateadd(day,0,getdate()))='Thursday' Then Dateadd(day,0,getdate())
END as duedate_Weekdue;
Andrea
  • 11,801
  • 17
  • 65
  • 72
mesapiegrande
  • 35
  • 1
  • 5

3 Answers3

5

You can reduce that to one line of code that uses a little math, and some SQL Engine trivia.

The answers that depend on DATEPART return non-deterministic results, depending on the setting for DATEFIRST, which tells the SQL Engine what day of the week to treat as the first day of the week.

There's a way to do what you want without the risk of getting the wrong result based on a change to the DATEFIRST setting.

Inside SQL Server, day number 0 is January 1, 1900, which happens to have been a Monday. We've all used this little trick to strip the time off of GETDATE() by calculating the number of days since day 0 then adding that number to day 0 to get today's date at midnight:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()),0)

Similarly, day number 3 was January 4, 1900. That's relevant because that day was a Thursday. Applying a little math to the number of days, and relying on the DATEDIFF function to drop fractions, which it does, this calculation will always return the next Thursday for you:

SELECT DATEADD(DAY, (DATEDIFF(DAY, 3, GETDATE())/7)*7 + 7,3);

Credit to this answer for the assist.

So your final query comes down to this:

Select 
getdate() as duedate,
datepart(yy,getdate()) as duedate_yr,
datepart(ww,getdate()) as duedate_ww,
DATEADD(DAY, (DATEDIFF(DAY, 3, GETDATE())/7)*7 + 7,3) as duedate_Weekdue;
Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
1

If the first day of the week is Sunday, by using the modulo operator %:

cast(dateadd(day, (13 - datepart(dw, getdate())) % 7, getdate()) as date)  as duedate_Weekdue

I also applied the casting of the result to date.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • this answer is dependent upon the @@DATEFIRST setting of sql server and would only match to Thursday if @@DATEFIRST is 7 – Matt Mar 20 '19 at 18:06
  • I mention it in my answer. And with a change depending on the setting can give the right result. – forpas Mar 20 '19 at 18:07
  • got it, just specifying @@DATEFIRST to make the place to set the "first day of the week" obvious to a less initiated individual if they happen upon your answer. I think it is a creative and decent answer I just don't like the dependence on @@DATEFIRST from a personal perspective. – Matt Mar 20 '19 at 18:11
  • This works well although I believe it should use "12-...." rather than "13-...". Thank you. – mesapiegrande Mar 20 '19 at 20:28
  • I tested it as I mention in my answer, for the setting Sunday as first day of the week. – forpas Mar 20 '19 at 20:56
0

Try identifying number of day in week with DATEPART and then adding enough days to go to next thursday:

declare @dt date = '2019-03-22'
declare @weekDay int

SELECT @weekDay =  DATEPART(dw, @dt)  

if @weekDay <= 5 
    select DATEADD(day, 5 - @weekDay ,@dt)
else
    select DATEADD(day,  12 - @weekDay  ,@dt)
Andrea
  • 11,801
  • 17
  • 65
  • 72