On the Trail of the ISO Week

  • unisense_nl

    SSC Journeyman

    Points: 92

    Maybe, other people like this function

    GO

    CREATE FUNCTION [dbo].[GetDateTimeOfIsoWeek](@WeekYearNumber int, @DayNumber int)

    RETURNS datetime

    AS

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

    -- GetDateTimeOfIsoWeek

    --

    -- According to ISO 8601, the first week of the year (week 1) is the week

    -- that contains at least the first four days of the year. In other words,

    -- the week that contains the first Thursday of a year is also week 1 of that year.

    -- Also note that according to ISO 8601 a week always starts on a Monday.

    -- if 01-01=Monday  then 01-04=Thursday -> 01-01=week  1 AND 01-04=week 1

    -- if 01-01=Thuesday then 01-04=Friday -> 01-01=week  1 AND 01-04=week 1

    -- if 01-01=Wednesday then 01-04=Saterday -> 01-01=week  1 AND 01-04=week 1

    -- if 01-01=Thursday then 01-04=Sunday -> 01-01=week  1 AND 01-04=week 1

    -- if 01-01=Friday  then 01-04=Monday -> 01-01=week 52 AND 01-04=week 1

    -- if 01-01=Saterday then 01-04=Thuesday -> 01-01=week 52 AND 01-04=week 1

    -- if 01-01=Sunday  then 01-04=Wednesday-> 01-01=week 52 AND 01-04=week 1

    --

    -- 4th jan. of indicated year is always in week 1

    -- week 1 always starts on Monday, just like other weeks

    -- 4th jan. must be Thursday, Friday, Saterday or Sunday if 01-01 is in week 1

    -- 1th jan. must be Monday, Thuesday, Wednesday or Thursday if 01-01 is in week 1

    --

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

    -- Created On:  19 december 2006

    -- Created By:  martijn Schuurmans

    --  Variables:

    --  * @WeekYearNumber:

    --     like 200752, 200453, etc.

    --  * @DayNumber: 

    --     1 = Monday

    --     2 = Thuesday

    --     3 = Wednesday

    --     4 = Thursday

    --     5 = Friday

    --     6 = Saterday

    --     7 = Sunday

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

    BEGIN

     -- Declare the return variable here

     DECLARE @DayId int

     DECLARE @WeekNumber int

     DECLARE @YearNumber int

     DECLARE @FunctionResult datetime

     -- validate input, we don't like negative WeekYear numbers

     SET @WeekYearNumber = ABS(@WeekYearNumber)

     IF @DayNumber < 1

      SET @DayNumber = 1

     IF @DayNumber > 7

      SET @DayNumber = 7

     -- get WeekId without Year Number

     SET @WeekNumber = right(@WeekYearNumber, 2)

     -- get YearId from @YearNumber

     SET @YearNumber =

      CASE

      -- like: 52

      WHEN @WeekYearNumber < 100 THEN YEAR(GETDATE())

      -- like: 0752

      WHEN @WeekYearNumber < 10000 THEN (LEFT(YEAR(GETDATE()),2)*100) + left(@WeekYearNumber, 2)

      -- like: 200752

      ELSE LEFT(@WeekYearNumber, 4)

      END

     -- get datevalue of monday in week 1 of indicated year

     -- get datevalue of 1th jan. of indicated year

     SET @FunctionResult = CAST((CAST(@YearNumber AS char(4))+ '0101') AS datetime)

     -- determine day of week for 01-01-YearNumber

     -- make corrections for server settings (=@@DATEFIRST)

     SET @DayId = DATEPART(dw, @FunctionResult) + @@DATEFIRST - 1

     IF @DayId > 7 

      SET @DayId = @DayId - 7

     -- determine the correction that is needed on 01-01-YearNumber day to get to Monday of week 1

     -- @DayId: 1=Monday; 2=Thuesday; 3=Wednesday; 4=Thursday; 5=Friday; 6=Saterday; 7=Sunday

     SET @DayId =

      CASE @DayId

      WHEN 1 THEN 0  -- if weekday of 01-01=Monday    then: no correction

      WHEN 2 THEN -1  -- if weekday of 01-01=Thuesday  then: Monday of W1 is 1 day before 01-01

      WHEN 3 THEN -2  -- if weekday of 01-01=Wednesday then: Monday of W1 is 2 days before 01-01

      WHEN 4 THEN -3  -- if weekday of 01-01=Thursday  then: Monday of W1 is 3 days before 01-01

      WHEN 5 THEN 3  -- if weekday of 01-01=Friday    then: Monday of W1 is 3 days after 01-01

      WHEN 6 THEN 2  -- if weekday of 01-01=Saterday  then: Monday of W1 is 2 days after 01-01

      WHEN 7 THEN 1  -- if weekday of 01-01=Sunday    then: Monday of W1 is 1 day after 01-01

      END

     -- this is datevalue of monday in week 1 of indicated year

     SET @FunctionResult = DATEADD(dd, @DayId, @FunctionResult)

     

     -- get last day of week, this is always sunday

     -- and make correction for given @DayNumer

     SET @FunctionResult = DATEADD(dd, ((@WeekNumber * 7) + @DayNumber - 8), @FunctionResult)

     RETURN(@FunctionResult)

    END

     

     

  • ms-876980

    Valued Member

    Points: 61

    Hi,

    I've found this ISO-Week function everywhere on the NET, but It isn't correct. Try with 2008.08.31. It must be week35 instead of week36. Has anybody in the world a right fuction for this ????

  • Adrian Peakman

    SSC Enthusiast

    Points: 142

    Yes... mine work (see earlier in the thread)

    print dbo.fnISOYearWeek('Aug 31 2008')

    Gives 200835 😀

  • karol63

    Valued Member

    Points: 57

    Perfect, thanky you.

    It helped me a lot.

  • Jeff Moden

    SSC Guru

    Points: 996655

    Lookup CREATE FUNCTION in Books Online... Example "A" is a nice, short function that determines ISO week. It could probably be optimized to be an INLINE function for performance reasons or might be able to be optimized to simply be a formula instead of a function, but it's a whole lot shorter and easier to understand than some of the example code I've seen on this thread.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Jeff Moden (3/22/2010)


    Lookup CREATE FUNCTION in Books Online... Example "A" is a nice, short function that determines ISO week. It could probably be optimized to be an INLINE function for performance reasons or might be able to be optimized to simply be a formula instead of a function, but it's a whole lot shorter and easier to understand than some of the example code I've seen on this thread.

    The function in BOL depends on the setting of DATEFIRST, so it isn't all that useful as a universal function.

    This is a bit longer, but does not depend on the setting of DATEFIRST:

    ISO Week of Year Function

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

  • R.P.Rozema

    SSChampion

    Points: 12300

    I needed the iso week number too in SQL 2005. Looked for it in all the usual places (bol, forums, google, etc) and found many different implementations. Most however have shortcomings. Usually a combination of one or more of:

    - using date string manipulations (dependent on locale) or

    - dependent on datefirst setting or

    - implemented procedural.

    It must be possible to do better than that. So this is what I wrote.

    The following query calculates for a given (set of) date(s), the iso week number plus it's iso year. And it does not use any string manipulations/conversions, it is independent of datefirst and it can be put into a single select statement, a view or a cte. Even though SQL 2008 now supports datepart(isowk, ) which does the same thing, I hope someone will still find it useful for older versions:

    select d.date

    ,case

    when d.date < x.thisfirstmonday then datepart(year, x.prevjan4)

    when d.date >= x.nextfirstmonday then datepart(year, x.nextjan4)

    else datepart(year, x.thisjan4)

    end as isoyear

    ,case

    when d.date < x.thisfirstmonday then 1 + datediff(day, x.prevfirstmonday, d.date) / 7

    when d.date >= x.nextfirstmonday then 1

    else 1 + datediff(day, x.thisfirstmonday, d.date) / 7

    end isoweeknumber

    from (

    -- To demonstrate show week numbers for 14 days around today's date,

    -- Januari 1st and December 31st.

    select dateadd(day, 7 - t.n, dt.date) as date

    from (

    select dateadd(day, datediff(day, 0, getdate()), 0) as date

    union select dateadd(year, datediff(year, 0, getdate()), 0)

    union select dateadd(day, -1, dateadd(year, 1 + datediff(year, 0, getdate()), 0))

    ) dt

    cross join (select 1 as n union all select 2 union all select 3

    union all select 4 union all select 5 union all select 6

    union all select 7 union all select 8 union all select 9

    union all select 10 union all select 11 union all select 12

    union all select 13 union all select 14

    ) t

    ) d

    cross apply (

    select max(case t.n when -1 then x1.jan4 else 0 end) as prevjan4

    ,max(case t.n when 0 then x1.jan4 else 0 end) as thisjan4

    ,max(case t.n when 1 then x1.jan4 else 0 end) as nextjan4

    ,max(case t.n when -1 then x2.jan4weekday else 0 end) as prevjan4weekday

    ,max(case t.n when 0 then x2.jan4weekday else 0 end) as thisjan4weekday

    ,max(case t.n when 1 then x2.jan4weekday else 0 end) as nextjan4weekday

    ,max(case t.n when -1 then x3.firstmonday else 0 end) as prevfirstmonday

    ,max(case t.n when 0 then x3.firstmonday else 0 end) as thisfirstmonday

    ,max(case t.n when 1 then x3.firstmonday else 0 end) as nextfirstmonday

    from (

    select -1 as n union all select 0 union all select 1

    ) t

    cross apply (

    select dateadd(year, t.n + datediff(year, 0, d.date), dateadd(day, 3, 0)) as jan4

    ) x1

    cross apply (

    select (-2 + datepart(dw, x1.jan4) + @@datefirst) % 7 + 1 as jan4weekday

    ) x2

    cross apply (

    select dateadd(day, 1 - x2.jan4weekday, x1.jan4) as firstmonday

    ) x3

    ) x

    order by 1;



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • geoff5

    Hall of Fame

    Points: 3453

    I've been struggling through the logic of ISO 8601 myself. My solution does not look like anything I've seen in this thread yet, so I am posting my solution for consideration. The approach I took boils down to the following steps:

    1. Let TargetDate equal the date for which we want to know the ISO week value.

    2. Let CurrentThursday equal the date of the Thursday associated with the same week as the TargetDate (taking into account that Sunday belongs with the prior Thursday).

    3. Let FirstThursday equal the date of the Thursday of the week containing January 4th, basing that on the year value of the CurrentThursday (not the TargetDate) and still taking into account that Sunday belongs with the prior Thursday. (Why January 4th? January 4th is always in the first ISO week of the year, which is not true for January 1st.)

    4. Let ISO_Week_Value equal the difference, in weeks, between the FirstThursday and the CurrentThursday, plus one.

    Here is a query that uses variables to reflect each step of the algorithm and includes two AllInOne versions that rely on only the @TargetDate variable and no others. The @DateOffset variable is used to make sure that Monday - Wednesday are always in the same week as the following Thursday and that Friday - Sunday are always in the same week as the prior Thursday no matter what the @@datefirst value is. The @ThursdayOffset variable is used to find Thursday no matter what the @@datefirst value is.

    declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime,

    @CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyint

    set @DateOffset = case when @@datefirst > 4 then @@datefirst - 8

    when @@datefirst > 1 then @@datefirst - 1 else 0 end

    set @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst

    else 12 - @@datefirst end

    set @TargetDate = getdate()

    set @CurrentThursday = dateadd(day, @ThursdayOffset -

    datepart(dw, @TargetDate + @DateOffset), @TargetDate + @DateOffset)

    set @FirstThursday = dateadd(day, @ThursdayOffset -

    datepart(dw, cast('1/4/' + datename(year, @CurrentThursday) as datetime)

    + @DateOffset), cast('1/4/' + datename(year, @CurrentThursday) as datetime)

    + @DateOffset)

    set @ISO_WeekNumber = datediff(week, @FirstThursday, @CurrentThursday) + 1

    select

    TestDate = @TargetDate,

    CurrentThursday = @CurrentThursday,

    FirstThursday = @FirstThursday,

    ISO_WeekNumber = @ISO_WeekNumber,

    ISO_WeekYear = year(@CurrentThursday),

    ISO_WeekNumber_AllInOne = datediff(week, dateadd(day, case

    when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

    datepart(dw, cast('1/4/' + datename(year, dateadd(day, case

    when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

    datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8

    when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case

    when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

    @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then

    @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end),

    cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then

    5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case

    when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

    @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then

    @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))

    as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when

    @@datefirst > 1 then @@datefirst - 1 else 0 end), dateadd(day, case when

    @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

    datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8

    when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case

    when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

    @@datefirst - 1 else 0 end)) + 1,

    ISO_WeekYear_AllInOne = year(dateadd(day, case when @@datefirst < 5 then

    5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate +

    case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

    @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then

    @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    This one is fairly short and I did a lot of testing to verify it works OK:

    ISO Week of Year Function

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

    This has the week and day of week included:

    ISO Year Week Day of Week Function

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515Returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed.

    There are a number of columns with ISO week support in this date table function:

    Date Table Function F_TABLE_DATE:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

    ISO_YEAR_WEEK_NO - ISO 8601 year and week in format YYYYWW

    Example = 200403

    ISO_WEEK_NO - ISO 8601 week of year in format WW

    Example = 52

    ISO_DAY_OF_WEEK - ISO 8601 Day of week number, Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7

    ISO_YEAR_WEEK_NAME - ISO 8601 year and week in format YYYY-WNN

    Example = 2004-W52

    ISO_YEAR_WEEK_DAY_OF_WEEK_NAME - ISO 8601 year, week, and day of week in format YYYY-WNN-D

    Example = 2004-W52-2

    Edit: Didn't see that this is an old thread and that I had already posted on it. :doze:

Viewing 9 posts - 16 through 24 (of 24 total)

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