Need some DateDiff calculating a week starting on Monday

Question

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

Possible duplicate of [How do you get the "week start date" and "week end date" from week number in SQL Server?](https://stackoverflow.com/questions/1267126/how-do-you-get-the-week-start-date-and-week-end-date-from-week-number-in-sql) — RoMEoMusTDiE, Apr 10 '18 at 20:24
Tip: `GetDate()` is treated a bit oddly in queries. Each _instance_ will have a constant value within a query. For example `select GetDate() as D1, GetDate() as D2 from SomeTable` may return two different values for the two columns, but they will not vary from one row to the next. When using multiple calls to `GetDate()`, whether in a single statement or multiple statements, one may avoid interesting surprises by getting a single value and using it throughout, i.e. `declare @Now as DateTime = GetDate();` and use `@Now` as needed. — HABO, Apr 10 '18 at 21:22
*I realized that SQL Server considers the beginning of the week to be on Sunday - no matter what.* No, not *no matter what*. This is culture related... — Shnugo, Apr 11 '18 at 12:40

Shnugo · Accepted Answer · 2018-04-11T11:14:33.183

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);

Thank you all and thank you Shnugo. This last one using @@DATEFIRST seems to do exactly what I was looking for and return the date for the Monday of a given week. I appreciate! — Tleilax, Apr 11 '18 at 13:08

Zorkolot · Answer 2 · 2018-04-11T13:31:00.937

2

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')

edited Apr 11 '18 at 13:31

answered Apr 10 '18 at 20:43

Zorkolot

1,899
1
11
8

Better answer than mine, I knew that trick for months not sure where my brain went. Looks like OP was very close, just needs to change his -1 to 0 and he'll be fine. – Anthony Hancock Apr 10 '18 at 21:00
Yes but I definitely recommend the second form, since it is so much more self-documenting. – Aaron Bertrand Apr 10 '18 at 21:23
2

I just compared your solution (which I like a lot) with mine and found them both equally fast. But - by incidence - I encountered a flaw: Try this `SELECT DATEADD(WEEK, DATEDIFF(WEEK, '1900-01-01', '2018-04-01'), '1900-01-01')`. On Sundays you'll get the **next** Monday as answer... – Shnugo Apr 11 '18 at 11:12
1

@Zorkolot, well, your edit works. but the solution's elegance somehow disappeared :-D And there is a BIG flaw: This is depending on culture/language. In my environment "Sunday" is "Sonntag"... – Shnugo Apr 11 '18 at 14:14
@Zorkolot You might check `sys.syslanguages` and read the `days` with `REVERSE` and `SUBSTRING` with `CHARINDEX`... Okay, I'm joking ;-D – Shnugo Apr 11 '18 at 14:16
@Shnugo `SELECT DATEPART(dw, '2018-04-01')` is an alternative, where Sunday = 0. I used `DATENAME` for clarity, in the end it is syntactic sugar. – Zorkolot Apr 11 '18 at 20:14
@Zorkolot cannot test this at the moment, but I'm afraid this is culture dependant... – Shnugo Apr 11 '18 at 20:19
1

@Shnugo Very interesting Shnugo! I've tested and it does change `DATEPART`. – Zorkolot Apr 11 '18 at 20:27
@Zorkolot, well you might add some correction logic with `@@DATEFIRST` to the return value of `DATEPART(dw,...)`, but this would get rather ugly... – Shnugo Apr 12 '18 at 11:00

score 0 · Answer 3 · answered Apr 10 '18 at 20:36

0

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

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

answered Apr 10 '18 at 20:36

Anthony Hancock

911
6
13

Try this with `SET LANGUAGE GERMAN;` and `SET LANGUAGE ENGLISH;` – Shnugo Apr 11 '18 at 07:49

Need some DateDiff calculating a week starting on Monday

3 Answers3