Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 9 down vote accepted

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:

http://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

share|improve this answer
    
Technically all I should have to do is change my DimCalendar table unfortunately our developers decided not to use it in several report instances so it is being calculated on the fly. By in large this was more an exercise in curiosity than it is any sort of crisis. –  Zane Mar 27 '13 at 16:06
3  
I would suggest you have your developers update their reports to follow best practices rather than cowboy coding. But that's just me. –  Aaron Bertrand Mar 27 '13 at 16:16
1  
The good news is in less than a week they won't be my developers anymore. :) –  Zane Mar 27 '13 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.