CODEWITHSUNDEEP
sql-servert-sql
Tleilax
Tleilax

Reputation: 75

Need some DateDiff calculating a week starting on Monday

I have the following T-SQL. I tried to use to calculate the date for the beginning of the week. I need my week to start on Monday:

SET DATEFIRST 1 -- Monday (First Week day)

DECLARE @dt DATETIME2

SELECT DATEADD(week, (-1) * (datepart(dw, @dt) - 1), @dt) AS datetime2--(7)

DECLARE @SchDT Datetime
DECLARE @TstPeriod varchar(20)
SET @TstPeriod = 'Week'

IF @TstPeriod = 'Month'
    SET @SchDT = cast(DATEADD(Month, DATEDIFF(month,0,GETDATE()),-1) as datetime2(7))

IF @TstPeriod = 'Week'
    SET @SchDT = cast(DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) as datetime2(7))

SELECT @SchDT

Only after this, I realized that SQL Server considers the beginning of the week to be on Sunday - no matter what.

Has anyone come across a similar need and could share how to make SQL Server calculate datediff() using Monday as beginning of the week?

Thank you very much

Upvotes: 3

Views: 2086

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

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

Should give you this week's Monday.

This is equivalent to:

SELECT DATEADD(WEEK, DATEDIFF(WEEK, '1900-01-01', GETDATE()), '1900-01-01')

Since Jan 1, 1900 was a Monday adding weeks to that date returns future mondays.

As Shnugo pointed out, if getdate() returns a Sunday this will actually apply to the following Monday. You can use a function to check what day it is and manipulate the date as necessary.

An example using DATENAME:

SELECT DATEADD(WEEK, DATEDIFF(WEEK, '1900-01-01', CASE WHEN DATENAME(WEEKDAY, GETDATE()) = 'Sunday' THEN DATEADD(DAY, -1, GETDATE()) ELSE GETDATE() END), '1900-01-01')

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You should not use SET DATEFIRST (or SET LANGUAGE) for the whole batch. This might have various unexpected sideeffects.

There is @@DATEFIRST, reflecting the first day of the week as INT value. Use this to compute your value independantly.

--You can check the difference 

SET LANGUAGE GERMAN;
SELECT @@DATEFIRST; 
SET LANGUAGE ENGLISH;
SELECT @@DATEFIRST;

--I set a variable

DECLARE @d DATETIME=GETDATE();

--Simple computation like suggestion in another answer will return different days depending on the system's culture

SET LANGUAGE GERMAN;
SELECT DATEADD(DAY,-DATEPART(WEEKDAY,@d)+2,@d);
SET LANGUAGE ENGLISH;
SELECT DATEADD(DAY,-DATEPART(WEEKDAY,@d)+2,@d);

--But using @@DATEFIRST you can get a correction term into your calculation 

SET LANGUAGE GERMAN;
SELECT DATEADD(DAY,-((DATEPART(WEEKDAY,@d) + @@DATEFIRST + 5) % 7),@d);
SET LANGUAGE ENGLISH;
SELECT DATEADD(DAY,-((DATEPART(WEEKDAY,@d) + @@DATEFIRST + 5) % 7),@d);

Upvotes: 4

Anthony Hancock
Anthony Hancock

Reputation: 931

This should get you the monday of the week of your date.

SELECT CONVERT(DATE,DATEADD(day,-DATEPART(weekday,@dt)+2,@dt))

Upvotes: 0

Related Questions