ISO Week vs SQL Server Week

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

There are several authorities assuming different conditions for the first week of the year. Some assumes first day of the week starts the first week but the most common idea is that the first week which has the first Thursday is the first week of the year.

So ISO_WEEK accepts that and like in 2010, 2011 or 2012 as you can check that ISO_WEEK says 1st January is 52nd or 53rd week while WEEK or WK or WW says they are the first week.

SELECT DATEPART (WW,'01/01/2010')   --> 1
SELECT DATEPART (WK,'01/01/2010')   --> 1
SELECT DATEPART (WEEK,'01/01/2010')   --> 1
SELECT DATEPART (ISO_WEEK,'01/01/2010')   --> 53