datepart week - 53 and 54 weeks?

• coffeejs

  SSC Veteran

  Points: 262

  More actions

  December 18, 2010 at 2:47 am

  #146561

  Hi everyone,

  I'm on a standard SQL Server 2005 System (US English), but I'm getting some strange results when using

  datepart(week, date)

  Let's say I have the following

  select

  datepart(day, '2028-12-31 00:00:00.000') as 'Day',

  datepart(month, '2028-12-31 00:00:00.000') as 'Month',

  datepart(week, '2028-12-31 00:00:00.000') as 'Calendar Week'

  go

  This will give me

  Day 31

  Month 12

  Calendar Week 54

  Even if I explicitly specify the locale as indicated in the code snippet below, I still get

  54 weeks.

  set language us_english

  go

  select

  datepart(day, '2028-12-31 00:00:00.000') as 'Day',

  datepart(month, '2028-12-31 00:00:00.000') as 'Month',

  datepart(week, '2028-12-31 00:00:00.000') as 'Calendar Week'

  go

  Does anyone have any ideas?

  I did find another thread for SQL Server 2000 with a similar issue, but from what I gather, that's for another locale, not US settings.

  http://www.sqlservercentral.com/Forums/Topic8295-5-1.aspx

  Thanks,

  James

• LutzM

  SSC Guru

  Points: 107049

  More actions

  December 18, 2010 at 3:46 am

  #1264347

  The reason is simple (more or less):

  A new week will start on Sunday as per the standard setting of SQL Server (U.S. English).

  Jan 1st 2028 is a Saturday. Jan 2nd is already week 2.

  The year 2028 include Feb 29, so 2028 has 366 days which is equal to 52 weeks and two days.

  And since the first week only has one day (Jan 1st) we'll end up with another week with just one day (Dec 31st).

  As long as you intentionally use SQL Servers method to calculate the week, you'll have 54 weeks for 2028.

  ---

  
  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]

• Paul White

  SSC Guru

  Points: 150467

  More actions

  December 18, 2010 at 4:21 am

  #1264351

  SQL Server 2008 onwards adds ISO_WEEK to the range of acceptable DATEPARTs:

  SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);

  SELECT DATEPART(WEEK, CURRENT_TIMESTAMP);

  In 2005, you would have to write your own implementation.

  More information: http://msdn.microsoft.com/en-us/library/ms174420(v=SQL.100).aspx

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• coffeejs

  SSC Veteran

  Points: 262

  More actions

  December 18, 2010 at 5:42 am

  #1264356

  Thank you LutzM, SQLkiwi, that helps.