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