You are here

sql

SQL: year of an ISO week

In MS-SQL you can get the ISO 8601 week date easily by using something like:

SELECT DATEPART(isoww, '2012-01-01')

This will tell you that January 1st of 2012 belongs to week 52. But it doesn't tell you that it belongs to year 2011! The shortest way to get the correct year is:

SELECT YEAR(DATEADD(day, 26 - DATEPART(isoww, '2012-01-01'), '2012-01-01'))

This will add some days to the date if it belongs to a week with a low number. It will subtract some days if it belongs to a week with a high number. The year of that date will always be the correct "ISO week year".

Subscribe to RSS - sql