locked
ISO Week Date FUnction RRS feed

  • Question

  • Here is an intresting dilema which i am trying to resolve, may be some one can help me.

    I am trying to Calculate week number along in Date table. I have two week column, One that starts with Sunday and One starts with Monday.

    the query in calendar table is loaded with Sunday as Start of the week, their is a data set were people would like to use Monday as begining of the week.

    Isowk function does give me the flexibility of getting 1st Day of the week as Monday.. .apparently when i concatenate the year it takes the year of previous year when rolling over two next year.

    here is the function i am using but concatenation beats me up to what i am trying to achieve.

    Monday as first day of the week.

    ,'W' + Right(DATEPART(isowk,Calendar_Date),2) + ' - ' + LEFT(Year_Week_Num, 4) as ISOwk

    • Moved by Tom Phillips Tuesday, January 29, 2013 9:24 PM TSQL question
    Tuesday, January 29, 2013 6:57 PM

Answers

  • Finally i was able to create it creating columns... using modulo function.

    Posting result set in case it helps anyone else.

                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) AS INT) AS Isoyear,
                             
                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
                             +Right('0'+(CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar)),2) 
                             AS INT) AS Isoweek,
                             
                             
                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +Right('0'+(CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar)),2) 
    +Right('0'+cast((DATEPART(weekday,Calendar_Date)+5) % 7+1 as nvarchar),2)as int)IsoweekyearNumber
                             
    ,                         
                             cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +'-'+ 'W' +CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar) 
    + '-'+cast((DATEPART(weekday,Calendar_Date)+5) % 7+1 as nvarchar) as ISOWeek_Number
    ,                        cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +'-'+ 'W' +CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar) as ISOWeek_Number_wk

    • Marked as answer by Iric Wen Wednesday, February 6, 2013 8:07 AM
    Friday, February 1, 2013 4:06 AM

All replies

  • I'm a bit uncertain about what is your concern, but note that there are two differences between WEEK and ISOWK. With WEEK, week 1 is the week in which Jan 1st falls. And Dec 31st is always in week 53 or 54.

    With ISO Week, week 1 is the week in which Jan 4 th falls, and Jan 1st and Dec 31st are in the same week, unless Jan 1st is on a Monday.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 29, 2013 11:10 PM
  • The weeknumber is a differently as the day of the week.

    In ISO 8601:
    the week goes from Monday to Sunday.
    The first week of the year is the first week wich has at least 4 days. This can also mean that the last few days of the passing year can belong to the 1st week, following year. This last for example Monday, 31st Dezember was in week 1/2013.

    If you want stuff to be easy, I can only advise to not try to solve that with a function. Instead make two tables like:

    ISO-Weeks:
    NrOfWeek, INT
    YearOfWeek, INT
    StartOfWeek, DATE (set to monday)
    EndOfWeek, DATE (set to sunday)

    Non-ISO-Weeks:
    NrOfWeek, INT
    YearOfWeek, INT
    StartOfWeek, DATE (set to a sunday)
    EndOfWeek, DATE (set to monday)

    NrOfWeek + YearOfWeek togehter would make an ideal composed PrimaryKey.

    If you don't like having two lists, you can add another column indicating that this is a ISO/Non-Iso week and include it into the primary key (there will be one ISO week 01/2014 and one Non-ISO week 01/2014).

    That way you can just make a query (wich is propably cheaper then any UDF) and it is easier to show stuff in a per-week manner.

    Wednesday, January 30, 2013 12:39 AM
  • Finally i was able to create it creating columns... using modulo function.

    Posting result set in case it helps anyone else.

                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) AS INT) AS Isoyear,
                             
                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
                             +Right('0'+(CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar)),2) 
                             AS INT) AS Isoweek,
                             
                             
                             Cast(cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +Right('0'+(CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar)),2) 
    +Right('0'+cast((DATEPART(weekday,Calendar_Date)+5) % 7+1 as nvarchar),2)as int)IsoweekyearNumber
                             
    ,                         
                             cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +'-'+ 'W' +CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar) 
    + '-'+cast((DATEPART(weekday,Calendar_Date)+5) % 7+1 as nvarchar) as ISOWeek_Number
    ,                        cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,Calendar_Date)+5) % 7+3,Calendar_Date)) as nvarchar) 
    +'-'+ 'W' +CAST(DATEPART(ISO_WEEK,Calendar_Date) as nvarchar) as ISOWeek_Number_wk

    • Marked as answer by Iric Wen Wednesday, February 6, 2013 8:07 AM
    Friday, February 1, 2013 4:06 AM