T-SQL beginning of week returns incorrect date

user_4071630 0
Viewed 318

I am trying to group a query by the beginning of each week which should be a Monday. I have various references from the internet to get the first day of the week by using the following code: 

DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)

However, this returns an incorrect result as follows:

SET DATEFIRST 7

DECLARE @Date DATE
SET @Date = '1/7/2018'

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)

and the result is '1/8/2018'. Obviously the week should not start the day after the date in question.

I have tried setting the day of the week via the "SET DATEFIRST = 1", but this has no impact

Here is an example of what I am trying to accomplish:

SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, m.MeasureDT), 0) as StartOfWeek,
    SUM(m.NumeratorVAL) AS Numerator
FROM 
    Database.Schema.Table AS m
GROUP BY 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, m.MeasureDT), 0)
ORDER BY 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, m.MeasureDT), 0)

As a result, each week starts on a Monday, but it includes the prior Sunday. Is there some time setting at a system level or SQL Server level I am missing or is there a flaw in the logic? Any help would be greatly appreciated.

datediff dateadd sql-server
2 Answers

You can adjust your date backward by one day prior to using the regular logic:

dateadd(day, -1, m.MeasureDT)

Or just do it in one step as explained here: https://www.itprotoday.com/sql-server/normalizing-first-day-week

So off the top of my head, "go back to prior Monday" works out to (dependent on the @@datefirst setting):

dateadd(day, datepart(weekday, m.MeasureDT) - 1, m.MeasureDT)

Unless I'm mistaken, you get to avoid the modular arithmetic since the range of datepart() is from 1 to 7, and after offsetting by 1 none of that range falls under zero.

user_13222680 
Try this 

DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)-1
user_92912010
Related