How to get ISO Year for ISO Week

SQL icon baseIf you need calculate week number from date, you can use DATEPART with parameter ISO_WEEK. Unfortunately there is nothing like ISO YEAR parameter. In case you need to show YearWeek value in some report you have to take care about correct value manually.

There are 2 cases when you cannot use year value directly. First when January 1-3 may belong to the previous year. Second when December 29-31 may belong to the next year. 

In code you can manage this way

CASE
WHEN (DATEPART(MONTH, [Date]) = 1 AND DATEPART(ISO_WEEK, [Date]) > 50)
-- Special cases: Jan 1-3 may belong to the previous year
THEN 'Y' + Cast(Right(DATEPART(yy, [Date])-1, 2) as varchar) + 'W' + Right('0' + Cast(DATEPART(ISO_WEEK, [Date]) as varchar), 2)
WHEN (DATEPART(MONTH, [Date]) = 12 AND DATEPART(ISO_WEEK, [Date]) < 45)
-- Special case: Dec 29-31 may belong to the next year
THEN 'Y' + Cast(Right(DATEPART(yy, [Date])+1, 2) as varchar) + 'W' + Right('0' + Cast(DATEPART(ISO_WEEK, [Date]) as varchar), 2)
ELSE
'Y' + Cast(Right(DATEPART(yy, [Date]), 2) as varchar) + 'W' + Right('0' + Cast(DATEPART(ISO_WEEK, [Date]) as varchar), 2)
END

If you need it repeatly, like for calendar table, you can create simple User Defined Function

CREATE FUNCTION dbo.ISO_YEAR @Date DATETIME
RETURNS INT
AS
BEGIN
DECLARE @ISOyear INT = DATEPART(YEAR, @Date);

-- WHEN January 1-3 may belong to the previous year
IF (DATEPART(MONTH, @DATE) = 1 AND DATEPART(ISO_WEEK, @DATE) > 50)
SET @ISOyear = @ISOyear - 1;

-- WHEN December 29-31 may belong to the next year
IF (DATEPART(MONTH, @DATE) = 12 AND DATEPART(ISO_WEEK, @DATE) < 45)
SET @ISOyear = @ISOyear + 1;

RETURN @ISOYear;

END

Thanks to GarethD from StackOverflow for his UDF function and second condition I didn't realized.

Sources:
DATEPART (Transact-SQL)
Difficulties comprehending ISO_week and week in Microsoft SQL


Add comment

Security code
Refresh