Interesting datepart results

  • P Jones


    Points: 12323

    Trying this

    set datefirst 7


    select DATEPART(week,convert(date,'31/12/2010',103)) as weekno,

    DATEPART(iso_week,convert(date,'31/12/2010',103)) as isono,

    Datepart(weekday,convert(date,'31/12/2010',103)) as DayNo,

    convert(date,'31/12/2010',103) as actualdate


    select DATEPART(week,convert(date,'01/01/2011',103)) as weekno,

    DATEPART(iso_week,convert(date,'01/01/2011',103)) as isono,

    Datepart(weekday,convert(date,'01/01/2011',103)) as DayNo,

    convert(date,'01/01/2011',103) as actualdate


    select DATEPART(week,convert(date,'02/01/2011',103)) as weekno,

    DATEPART(iso_week,convert(date,'02/01/2011',103)) as isono,

    Datepart(weekday,convert(date,'02/01/2011',103)) as DayNo,

    convert(date,'02/01/2011',103) as actualdate


    select DATEPART(week,convert(date,'03/01/2011',103)) as weekno,

    DATEPART(iso_week,convert(date,'03/01/2011',103)) as isono,

    Datepart(weekday,convert(date,'03/01/2011',103)) as DayNo,

    convert(date,'03/01/2011',103) as actualdate

    order by actualdate

    gives this

    weekno isono DayNo actualdate

    53 52 6 2010-12-31

    1 52 7 2011-01-01

    2 52 1 2011-01-02

    2 1 2 2011-01-03

    so we now have 1 day weeks or travel back in time :w00t:

    datepart(iso_week... must ignore dateformat and always start on a Monday

    but why does datepart(week... give a one day week for 1st Jan???

  • LutzM

    SSC Guru

    Points: 107049

    Just check the definitions (straight from BOL):

    ISO_WEEK datepart : ...Each week is associated with the year in which Thursday occurs. ...

    and for DATEPART(week):

    ...When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

    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....

    Yes, there is a chance of a one day week at the beginning of a year when using DATEPART(week), but not with ISO_Week. Regarding your second question: as per the ISO standard, a week always start on Monday. With DATEPART(week) it depends on the setting of DATEFIRST or LANGUAGE.

    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

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