CODEWITHSUNDEEP
sqlsql-serversql-server-2008
fancyPants
fancyPants

Reputation: 51888

Setting start of week to monday - datefirst has no effect

Consider following script:

SET LANGUAGE 'German'
GO
SET DATEFIRST 1
GO

DECLARE @FullDate DATETIME
DECLARE @DayofWeek tinyint
DECLARE @relativeWeek int

SELECT @FullDate  = '5/1/2008'
WHILE (@FullDate <= '7/1/2008')
BEGIN

SELECT DATEPART (DW , @FullDate) as day_of_week, DATEDIFF(WEEK, CURRENT_TIMESTAMP, @FullDate) as relative_week

SELECT @FullDate = DATEADD(DAY, 1, @Fulldate)
END

The result currently is

day_of_week   relative_week
----------------------------
6   -220
7   -219
1   -219

Now how do I manage to get the result like this:

day_of_week   relative_week
----------------------------
6   -220
7   -220 <-- here's the difference :) 
1   -219

Meaning to have the start of the week on monday / day 1 of week. As you can see setting the datefirst variable to 1 doesn't have an effect.

Thanks in advance.

Upvotes: 0

Views: 1398

Answers (1)

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Refer to this: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8cc3493a-7ae5-4759-ab2a-e7683165320b/

Hope it helps.

As a fact - DATEDIFF doesn't care about DATEFIRST

Upvotes: 3

Related Questions