CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Eugene Karashevich
Eugene Karashevich

Reputation: 31

SQL Server: change first day of week to Sunday using DATEPART with ISOWK parameter

I need to get week numbers for some set of dates. For example, for Jan 2016 it should be something like:

[Week Number]
53             <--- for dates from Jan 1 to Jan 2
1              <--- for dates from Jan 3 to Jan 9
2              <--- for dates from Jan 10 to Jan 16
...            <--- etc.

As you see, week should start from Sunday. I've used following function to set start day:

SET DATEFIRST 7

And this one to get week number:

DATEPART(ISOWK, SOME_DATE)

But I've noticed that DATEFIRST doesn't affect results when ISOWK being used - week always starts from Monday.

So, what am I doing wrong? Thank you!

UPD:

I've made a function, seems it works good:

CREATE FUNCTION [dbo].[GetWeekNumber]
( 
    @date as DATETIME,
    @offset as INT
)
RETURNS NVARCHAR(100)
    BEGIN 
        DECLARE @weekNumber as int
        DECLARE @year as int

        SET @date = DATEADD(MINUTE, @offset, @date)
        SET @year = DATEPART(year, DATEADD(DAY, 1 - DATEPART(WEEKDAY, @date), CAST(@date AS DATE)))

        IF @@DATEFIRST = 7 
            BEGIN
                SET @date = DATEADD(day, 1, @date)
            END
        SET @weekNumber = DATEPART(ISO_WEEK, @date)
        RETURN concat(@year, ', Week ', right('0' + convert(nvarchar, @weekNumber), 2))
    END

You need to pass the date and timezone offset (in minutes). And SET DATEFIRST @startDay before executing this function (1 - Monday, 7 - Sunday). Example:

DECLARE @date as DATETIME
SET @date = '2016-01-03 12:00'

SET DATEFIRST 1
SELECT dbo.GetWeekNumber(@date, 0) -- 2015, Week 53

SET DATEFIRST 7
SELECT dbo.GetWeekNumber(@date, 0) -- 2016, Week 01

Upvotes: 3

Views: 2984

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

This is by design. According to MSDN:

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004.

So ISOWK is always based on Thursday, and is not affected by DATEFIRST.

To get the results you want, just use WEEK instead of ISOWK.

UPDATE: Also from MSDN:

January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The only way to get Week #1 to be Jan 3-9 is to use math. Subtract one from the DATEPART(wk...) value, and if the result is 0, make it 53 (or simply use DATEADD on your date to subtract a week). There is no simpler way, as by design, the first week of any year will be the week that contains Jan 1.

Upvotes: 5

Related Questions