Interesting datepart results

• P Jones

  SSChampion

  Points: 12361

  More actions

  July 5, 2011 at 3:43 am

  #389895

  Trying this

  set datefirst 7

  go

  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

  union

  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

  union

  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

  union

  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

  weeknoisonoDayNoactualdate

  535262010-12-31

  15272011-01-01

  25212011-01-02

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

  More actions

  July 5, 2011 at 8:39 am

  #1348320

  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.

  ---

  
  Lutz
  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]