On the Trail of the ISO Week

  • Chris Hedgate

    One Orange Chip

    Points: 25041

  • John Valore

    SSC Veteran

    Points: 221

    I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

     

    Declare @startdate as smalldatetime

    set @startdate ='2005-27-01'

    SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr,

    CASE

    -- Exception where @startdate is part of week 52 (or 53) of the previous year

    WHEN @startdate <

    CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

    WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    END

    THEN

    (DATEDIFF(d,

    CASE (DATEPART(dw, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'

    WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

    END,

    @startdate

    ) / 7) + 1

    -- Exception where @startdate is part of week 1 of the following year

    WHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'

    WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

    END

    THEN 1

    ELSE

    -- Calculate the ISO week number for all dates that are not part of the exceptions above

    (DATEDIFF(d,

    CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

    WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

    END,

    @startdate

    ) / 7) + 1

    END AS IsoWeek

    A bit puzled about this 

    Best regards

     

    John Valore

     

     

     

  • Nic Washington

    SSC Veteran

    Points: 226

    I tried John's code - I had to change the @StartDate to '2005-01-27'.

    I had no problems - I received an ISO Week of "4"

    Regards,

    Nic Washington

  • John Valore

    SSC Veteran

    Points: 221

    Thanks for the reply Nic,

    I took some time before someone responded, so I had actually abandoned the approach and settled with the standard datepart function.

    Thanks to your response, I looked at the problem again and have now solved the problem. I still have to input the date in the format yyyy-mm-dd :

    set @startdate =CAST('2005-01-27' as smalldatetime)

    but if I include a statement :

    set dateformat mdy

    (default american date input/output), the code from Chris Hedgate now works.

    The devil is apparently still in the details and the several datetime formats.

    Thank you very much.

    John Valore

  • John Valore

    SSC Veteran

    Points: 221

    Hi,

    Just an update.

    I was going to use the functionality in an udf environment, but "forgot" that it is not possible to issue SET commands in an udf.

    So I had to look at Chris code again when it suddenly strooke me, that I should replace all the '-01-04' with '-04-01'.

    Bingo.

    John Valore

  • Henk Schreij

    SSCarpal Tunnel

    Points: 4553

    Cris:

    If you use the ISO format for dates (yyyymmdd), not the yyyy-mm-dd format, SQL Server is not vulnerable for myd or dmy dates.

    See CONVERT ISO 112 (or 12 for yymmdd) in BOL.

    SQL server accepts '20050323' (today, y=2005, m=03, d=23) either in Europe or USA.

  • rkintrup

    SSC Journeyman

    Points: 79

    Here is a version which uses CONVERT to allow using the yyyy-mm-dd format. This makes the code independent from SQL Server installation flavors.

    Ralf

    -----

    DECLARE @date datetime

    SET @date = CONVERT(datetime, '2003-12-31', 120) -- 1

    --SET @date = CONVERT(datetime, '2005-01-01', 120) -- 53

    --SET @date = CONVERT(datetime, '2005-01-05', 120) -- 1

    --SET @date = CONVERT(datetime, '2006-01-01', 120) -- 52

    SELECT @date,

    CASE

    -- Exception where @date is part of week 52 (or 53) of the previous year

    WHEN @date =

    CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)

    WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

    END

    THEN 1

    -- Calculate the ISO week number for all dates that are not part of the exceptions above

    ELSE (DATEDIFF(d,

    CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

    WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)

    WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

    END,

    @date) / 7) + 1

    END AS IsoWeek

  • Tomasz Kubacki

    Valued Member

    Points: 70

    Hmmm that is my function similiar to above but IMHO easier to understand

    CREATE FUNCTION ISOweek(@date datetime)

    RETURNS int

    AS

    BEGIN

    declare @dayOfWeek int

    declare @1ThISOWeek1ThDay datetime

    declare @4ThJan datetime

    declare @1ThJan datetime

    declare @31ThDec datetime

    declare @numberOfISOweeks int

    declare @Tmp int

    declare @returnValue as int

    set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-04' as datetime)

    set @1ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-01' as datetime)

    set @31ThDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '-12-31' as datetime)

    --"rule of thursday"

    if (DATEPART(dw, @1ThJan) = 5 or DATEPART(dw, @31ThDec) = 5 )

    set @numberOfISOweeks = 53

    else

    set @numberOfISOweeks = 52

    set @dayOfWeek = (select DATEPART(dw, @4ThJan) - 1)

    if @dayOfWeek = 0

    set @dayOfWeek = 7

    set @1ThISOWeek1ThDay = (select DATEADD(day, -(@dayOfWeek -1) ,@4ThJan))

    if @date @numberOfISOweeks

    set @returnValue = 1 -- or -1 as an error

    return @returnValue

    END

  • requena

    SSC Veteran

    Points: 292

    Sombody now how to calculate first monday day from given week

     

    For example I need get the day from a given week:

     

    ISO date ‘20050101’ is the week 53 in ISO 8601

     

    If a have the week number 53 how I can calculate the ISO date?

     

    Thank for all in advance.

  • Jeff Moden

    SSC Guru

    Points: 996444

    Better late than never, I guess...

    DECLARE @Year    CHAR(4)

    DECLARE @NextYear CHAR(4)

    DECLARE @ISOWeek VARCHAR(2)

        SET @Year    = '2001'

        SET @ISOWeek = '53'

        SET @NextYear = @Year+1

     

    SELECT

    CASE

    WHEN @ISOWeek > 0

     AND DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)

       < DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@NextYear),0)

    THEN DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)

    ELSE 0

    END AS StartDate,

           DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate

    --todo... needs some error checking for the week number...

    It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...

    I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • Adrian Peakman

    SSC Enthusiast

    Points: 142

    Hi,

    The ISO Week problem arised last year for us. I programmed, tested and benchmarked a set of various conversion routines. Speed was essential in my case.

    Above routine: 1360ms for 10000 conversions

    fnISOWeek: 513ms for 10000 conversions

    In general I first group results by SQL Year/Week then convert the grouped results using fnISOWeekFromSQL (very fast). Finally I regroup, as SQL separates the end and beginning weeks over a year end. This is faster in my case (millions of rows) than converting every row, especially in crosstabs.

    I thought I'd share them, though please do credit me if you use them!:

    fnISOWeek (@datetime) returns ISO week as an integer from SQL datetime

    fnISOYear (@datetime) returns ISO Year as an integer from SQL datetime

    fnISOYearWeek (@datetime) Returns the ISO Year Week in YYYYWW format of the given SQL date (useful for ordering data)

    fnISOWeekFromSQL (@week, @year) returns ISO week given the SQL DATEPART week/year

    fnISOYearWeekFromSQL (@week, @year) Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and Week

    fnISOWeekStartDate (@week, @year) Returns the date of the first day of the given ISO Week/Year (Monday)

    As we're talking date functions, I also have a very fast midnight calculation if anybody is interested (calculates "12/1/2006 00:00:00" from "12/1/2006 xx:xx:xx")

    Adrian Peakman

    PS (Added)

    Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )

    My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.

    ---------------------------------------

    CREATE FUNCTION fnISOWeek

    --Adrian Peakman 6/12/2004

    --Returns the ISO Week of the given date

    (@d datetime)

    returns int

    AS

    BEGIN

    DECLARE @ISOWeek int

    SET @ISOWeek= DATEPART(wk,@d)

    IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4

        begin

        SET @ISOWeek=@ISOWeek-1

        IF @ISOWeek=0

            begin

            SET @d = DATEADD(day,-3,@d)

            SET @ISOWeek= DATEPART(wk,@d)       

            IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4

                    SET @ISOWeek=@ISOWeek-1

            end

        end

    IF @ISOWeek=53

        IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28 SET @ISOweek=1

    Return (@ISOWeek)

    END

    --------------------------------

    CREATE FUNCTION fnISOYear

    --Adrian Peakman 6/12/2004

    --Returns the ISO Year of the given date

    (@d datetime)

    returns int

    AS

    BEGIN

    DECLARE @ISOWeek int

    DECLARE @iDayOfYear int

    DECLARE @iYear int

    SET @iDayOfYear=DATEPART(dy,@d)

    SET @iYear=DATEPART(Year,@d)

    IF @iDayOfYear > 3 AND @iDayOfYear < 363 RETURN(@iYear)

    --Okay now this date may be part of the current year, the last year or the next year!

    SET @ISOWeek= dbo.fnISOWeek(@d)

    IF @iDayOfYear4

        begin

        SET @ISOWeek=@ISOWeek-1

        IF @ISOWeek=0

            begin

            --SET @ISOWeek=dbo.fnISOWeek(DATEADD(day,-3,@d))

            SET @d = DATEADD(day,-3,@d)

            SET @ISOWeek= DATEPART(wk,@d)       

            IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4

                    SET @ISOWeek=@ISOWeek-1

           

            IF @ISOWeek >0 SET @ISOYear=@ISOYear-1

            end

        end

    IF @ISOWeek=53

        IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28

            BEGIN

            SET @ISOweek=1

            SET @ISOYear=@ISOYear+1

            END

    RETURN(@ISOYear*100+@ISOWeek)

       

    END

    -------------------------------

    CREATE FUNCTION fnISOWeekFromSQL

    --Adrian Peakman 6/12/2004

    --Returns the ISO Week given the SQL Week

        (

        @IntWeekNumber int,

        @IntYear int

        )

    RETURNS int

    As

    BEGIN

    DECLARE @ISOWeek int

    DECLARE @Jan1Day datetime

    SET @Jan1Day=DATEPART(dw,CONVERT(varchar,@IntYear))

    SET @ISOWeek=@IntWeekNumber

    IF @Jan1Day>4

        SET @ISOWeek=@ISOWeek-1

    IF @ISOWeek>52

        begin

        IF DATEPART(dw,CONVERT(varchar,@IntYear+1))0

        RETURN(@IsoWeek)

    SET @ISOWeek=53

    IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4

        SET @ISOWeek=@ISOWeek-1

    RETURN(@ISOWeek)

       

    END

    ---------------------------------

    CREATE FUNCTION fnISOYearWeekFromSQL

    --Adrian Peakman 6/12/2004

    --Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and Week

        (

        @intWeekNumber int,

        @intYear int

        )

    RETURNS int

    As

    BEGIN

    DECLARE @ISOWeek int

    DECLARE @ISOYear int

    DECLARE @Jan1Day datetime

    SET @Jan1Day=DATEPART(dw,CONVERT(varchar,@intYear))

    SET @ISOWeek=@intWeekNumber

    SET @ISOYear=@intYear

    IF @Jan1Day>4

        SET @ISOWeek=@ISOWeek-1

    IF @ISOWeek>52

        begin

        IF DATEPART(dw,CONVERT(varchar,@intYear+1))0

        RETURN(@ISOYear*100+@IsoWeek)

    SET @ISOWeek=53

    SET @ISOYear=@ISOYear-1

    IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4

        begin

        SET @ISOWeek=@ISOWeek-1

        end

    RETURN(@ISOYear*100+@ISOWeek)

       

    END

    ----------------------------------

    CREATE FUNCTION fnISOWeekStartDate

    --Adrian Peakman 6/12/2004

    --Returns the date of the first day of the given ISO Week/Year (Monday)

        (

        @IntWeekNumber int,

        @IntYear int

        )

    RETURNS DateTime

    As

    BEGIN

    DECLARE @StartDate DateTime

    DECLARE @Jan1Day int

    SET @StartDate=CONVERT(varchar,@IntYear)

    SET @Jan1Day=DATEPART(dw,@StartDate)

    IF @Jan1Day>4

        Set @StartDate=DATEADD(day,1-@Jan1Day+7*@IntWeekNumber,@StartDate)

    ELSE

        Set @StartDate=DATEADD(day,1-@Jan1Day+7*(@IntWeekNumber-1),@StartDate)

    RETURN(@StartDate)

       

    END

  • Adrian Peakman

    SSC Enthusiast

    Points: 142

    Incidentally the routine posted by rkintrup fails for me every few years:

    Can't test Tomasz's routine as if fails on my installation (British dates). I'd be interested to know if my routines fail on other installations than British, hopefully they shouldn't! (erk!)

    Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )

    My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.

    Thanks,

    Adrian

    Date fnISOWeek  Rkintrup

    002/01/1970   1   53

    09/01/1970   2   54

    16/01/1970   3   55

    23/01/1970   4   56

    30/01/1970   5   57

    06/02/1970   6   58

    13/02/1970   7   59

    20/02/1970   8   60

    27/02/1970   9   61

    06/03/1970   10   62

    13/03/1970   11   63

    20/03/1970   12   64

    02/01/1976   1   53

    09/01/1976   2   54

    16/01/1976   3   55

    23/01/1976   4   56

    30/01/1976   5   57

    06/02/1976   6   58

    13/02/1976   7   59

    20/02/1976   8   60

    27/02/1976   9   61

    05/03/1976   10   62

    12/03/1976   11   63

    19/03/1976   12   64

    02/01/1981   1   53

    09/01/1981   2   54

    16/01/1981   3   55

    23/01/1981   4   56

    30/01/1981   5   57

    06/02/1981   6   58

    13/02/1981   7   59

    20/02/1981   8   60

    27/02/1981   9   61

    06/03/1981   10   62

    13/03/1981   11   63

    20/03/1981   12   64

    02/01/1987   1   53

    09/01/1987   2   54

    16/01/1987   3   55

    23/01/1987   4   56

    30/01/1987   5   57

    06/02/1987   6   58

    13/02/1987   7   59

    20/02/1987   8   60

    27/02/1987   9   61

    06/03/1987   10   62

    13/03/1987   11   63

    20/03/1987   12   64

    02/01/1998   1   53

    09/01/1998   2   54

    16/01/1998   3   55

    23/01/1998   4   56

    30/01/1998   5   57

    06/02/1998   6   58

    13/02/1998   7   59

    20/02/1998   8   60

    27/02/1998   9   61

    06/03/1998   10   62

    13/03/1998   11   63

    20/03/1998   12   64

    02/01/2004   1   53

    09/01/2004   2   54

    16/01/2004   3   55

    23/01/2004   4   56

    30/01/2004   5   57

    06/02/2004   6   58

    13/02/2004   7   59

    20/02/2004   8   60

    27/02/2004   9   61

    05/03/2004   10   62

    12/03/2004   11   63

    19/03/2004   12   64

    02/01/2009   1   53

    09/01/2009   2   54

    16/01/2009   3   55

    23/01/2009   4   56

    30/01/2009   5   57

    06/02/2009   6   58

    13/02/2009   7   59

    20/02/2009   8   60

    27/02/2009   9   61

    06/03/2009   10   62

    13/03/2009   11   63

    20/03/2009   12   64

    02/01/2015   1   53

    09/01/2015   2   54

    16/01/2015   3   55

    23/01/2015   4   56

    30/01/2015   5   57

    06/02/2015   6   58

    13/02/2015   7   59

    20/02/2015   8   60

    27/02/2015   9   61

    06/03/2015   10   62

    13/03/2015   11   63

    20/03/2015   12   64

  • Bill the Cat

    Say Hey Kid

    Points: 689

    FYI, the article sites ISO6801, I believe the author is referring to ISO8601, and I have certainly run into this same kind of issue in respect to ensuring applications developed in different environments are compatible with 8601 defined week numbers and weekday numbers. 

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    Where does it say ISO 6801 in the article? I can't find any references to it. I know I had an error a long time ago, but that was changed to 8601.

    Re: the setting of DATEFIRST. Note this section from my article:

    First you should note that whenever the day of the week is checked for a specific date, we always make sure that the ISO standard of using Monday as the starting day of the week is used, regardless of the current setting of @@datefirst. This is accomplished by adding @@datefirst - 1 to the value returned by DATEPART(dw, ) and then dividing this number by 7. The remainder of the division is the day of the week for the specified date using Monday as the starting day of the week. Note though that Sunday becomes 0 with this expression, so if you need it to be 7 (for some calculation or whatever) you must exchange it for that.

    Finally, regarding performance, the fastest way I have found to get ISO 8601 week numbers is to use the built-in routines in Visual Basic.NET. Creating a user-defined function in Visual Basic.NET is very easy, so if you're running SQL Server 2005 that is an option.

  • Ian Cox-160558

    SSC Enthusiast

    Points: 132

    I like your method Tomasz, it leads to much cleaner looking queries.

    However, your code doesn't quite work.

    In particular you are subtracting 1 from the day of the week twice

    This modified version works

    CREATE FUNCTION dbo.fnISOweek(@date datetime)

    RETURNS int

    AS

    BEGIN

    declare @dayOfWeek int

    declare @1ThISOWeek1ThDay datetime

    declare @4ThJan datetime

    declare @1StJan datetime

    declare @31StDec datetime

    declare @numberOfISOweeks int

    declare @Tmp int

    declare @returnValue as int

    set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0104' as datetime)

    set @1StJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0101' as datetime)

    set @31StDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '1231' as datetime)

    --"rule of thursday"

    if (DATEPART(dw, @1StJan) = 5 or DATEPART(dw, @31StDec) = 5 )

    set @numberOfISOweeks = 53

    else

    set @numberOfISOweeks = 52

    set @dayOfWeek = DATEPART(dw, @4ThJan)

    set @1ThISOWeek1ThDay = DATEADD(day, -(@dayOfWeek -1) ,@4ThJan)

    if @date @numberOfISOweeks

    set @returnValue = 1 -- or -1 as an error

    return @returnValue

    END

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply