I needed the iso week number too in SQL 2005. Looked for it in all the usual places (bol, forums, google, etc) and found many different implementations. Most however have shortcomings. Usually a combination of one or more of:
- using date string manipulations (dependent on locale) or
- dependent on datefirst setting or
- implemented procedural.
It must be possible to do better than that. So this is what I wrote.
The following query calculates for a given (set of) date(s), the iso week number plus it's iso year. And it does not use any string manipulations/conversions, it is independent of datefirst and it can be put into a single select statement, a view or a cte. Even though SQL 2008 now supports datepart(isowk, ) which does the same thing, I hope someone will still find it useful for older versions:
select d.date
,case
when d.date < x.thisfirstmonday then datepart(year, x.prevjan4)
when d.date >= x.nextfirstmonday then datepart(year, x.nextjan4)
else datepart(year, x.thisjan4)
end as isoyear
,case
when d.date < x.thisfirstmonday then 1 + datediff(day, x.prevfirstmonday, d.date) / 7
when d.date >= x.nextfirstmonday then 1
else 1 + datediff(day, x.thisfirstmonday, d.date) / 7
end isoweeknumber
from (
-- To demonstrate show week numbers for 14 days around today's date,
-- Januari 1st and December 31st.
select dateadd(day, 7 - t.n, dt.date) as date
from (
select dateadd(day, datediff(day, 0, getdate()), 0) as date
union select dateadd(year, datediff(year, 0, getdate()), 0)
union select dateadd(day, -1, dateadd(year, 1 + datediff(year, 0, getdate()), 0))
) dt
cross join (select 1 as n union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11 union all select 12
union all select 13 union all select 14
) t
) d
cross apply (
select max(case t.n when -1 then x1.jan4 else 0 end) as prevjan4
,max(case t.n when 0 then x1.jan4 else 0 end) as thisjan4
,max(case t.n when 1 then x1.jan4 else 0 end) as nextjan4
,max(case t.n when -1 then x2.jan4weekday else 0 end) as prevjan4weekday
,max(case t.n when 0 then x2.jan4weekday else 0 end) as thisjan4weekday
,max(case t.n when 1 then x2.jan4weekday else 0 end) as nextjan4weekday
,max(case t.n when -1 then x3.firstmonday else 0 end) as prevfirstmonday
,max(case t.n when 0 then x3.firstmonday else 0 end) as thisfirstmonday
,max(case t.n when 1 then x3.firstmonday else 0 end) as nextfirstmonday
from (
select -1 as n union all select 0 union all select 1
) t
cross apply (
select dateadd(year, t.n + datediff(year, 0, d.date), dateadd(day, 3, 0)) as jan4
) x1
cross apply (
select (-2 + datepart(dw, x1.jan4) + @@datefirst) % 7 + 1 as jan4weekday
) x2
cross apply (
select dateadd(day, 1 - x2.jan4weekday, x1.jan4) as firstmonday
) x3
) x
order by 1;