CODEWITHSUNDEEP
sqlsql-servert-sqldate
mesapiegrande
mesapiegrande

Reputation: 35

SQL 'Round' Up a Date to a Given Day of the week

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;

Upvotes: 3

Views: 3967

Answers (3)

Eric Brandt
Eric Brandt

Reputation: 8101

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;

Upvotes: 5

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

Andrea
Andrea

Reputation: 12355

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)

Upvotes: 0

Related Questions