How to calculate the no of iso weeks in a year in t-sql?

Question

I need a simple query to calculate the no of iso weeks in any given year?

Answer 1

I think this should do the trick.

DECLARE @year smallint = 2015;

SELECT 
  TheYear = @year,
  ISOWeeks= MAX(DATEPART(ISOWK,DATEADD(DD,N,CAST(CAST(@year AS char(4))+'1223' AS date))))
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) t(N);

You could include this logic in a function like this:

CREATE FUNCTION dbo.CalculateISOWeeks(@year smallint)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT ISOWeeks = 
  MAX(DATEPART(ISOWK,DATEADD(DD,N,CAST(CAST(@year AS char(4))+'1223' AS date))))
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) t(N);

and use it like this:

SELECT ISOWeeks FROM dbo.CalculateISOWeeks(2014);

or better yet... because we're calculating a static value, why not just pop those values into a table then index it like this:

SELECT 
  Yr       = ISNULL(CAST(Yr AS smallint),0), 
  ISOWeeks = ISNULL(CAST(ISOWeeks AS tinyint),0)
INTO dbo.ISOCounts
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1949
  FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x),
       (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(x)
) Years(Yr)
CROSS APPLY dbo.CalculateISOWeeks(Yr+1950);

CREATE UNIQUE CLUSTERED INDEX uci_ISOCounts ON dbo.ISOCounts(Yr);

Now whenever you need to calculate the number of ISO weeks for a given year you can retrieve the pre-calculated value from your table via an index seek.

SELECT * FROM dbo.ISOCounts WHERE yr = 2014;

Results:

Yr     ISOWeeks
------ --------
2014   53