Sql-server – ISO Week vs SQL Server Week

sql serversql-server-2008

Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly according to the ISO standards. I ran this script as a test case.

SET DATEFIRST 1
SELECT DATEPART(WEEK, '3/26/13')
    , DATEPART(WEEK, '3/27/12')
    , DATEPART(WEEK, '3/20/12')
    , DATEPART(WEEK, '1/2/12')
SELECT DATEPART(ISO_WEEK, '3/26/13')
    , DATEPART(ISO_WEEK, '3/27/12')
    , DATEPART(ISO_WEEK, '3/20/12')
    , DATEPART(ISO_WEEK, '1/2/12')

When run I got these results.

ResultSet

I thought this was peculiar and so I did some more digging and found that SQL Server counts January 1st as the first week of the year where ISO counts the first Sunday in January as the first week of the year.

The question then ends up being two fold. Question 1 why is this? Question 2 is there any way to change this so I'm not having to modify all of my code to use ISO_Week everywhere?

Best Answer

Back when SQL Server first implemented the WEEK date/part, they had to make a choice. I don't think there was really much consciousness about it, except to align to the most common standard at the time - remember this was at a time where conforming to the standards was not a top priority (else we'd not have things like timestamp, IDENTITY and TOP). They later added ISO_WEEK (2008 I believe) because the workaround in the meantime was to write your own, slow, crappy scalar UDF - in fact they even created a really bad one and put it in the official documentation (it has since been removed as far as I can tell).

I don't know of a way to make DATEPART(WEEK pretend it is DATEPART(ISO_WEEK - I think you will have to change the code (and if you are using source control, this shouldn't be very hard - how many places are you performing this calculation? Have you thought about computing it somewhere so your code doesn't have to be riddled with it? Since you're changing the code now, this might be the time to consider this...).

And if you really want the answer to why? I think you'll have to grab some of the original developers to determine why they chose the default that they did. Again, I think it wasn't an actual "F the standards!" choice, but rather, "What standards?"

There is some information here that may be useful:

https://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server

Related Question