sqlsql-servert-sqldate

SQL Get "ISO Year" for ISO Week

I need to calculate the year a week is assigned to. For example the 29th December of 2003 (Monday) was assigned to week one of year 2004 (this is only for europe, I think). You can take a look at this with this code:

SELECT DATEPART(isowk, '20141229');

But now I need an easy way to get the year this week is assigned to. What I currently do is not that elegant:

DECLARE @week int, @year int, @date char(8)

--set @date = '20150101'
set @date = '20141229'


SET @week = cast(datepart(isowk, @date) as int)

if @week = 1
begin
      if DATEPART(MONTH, @date) = 12
      begin
            set @year = DATEPART(year, @date) + 1
      end
      else
      begin
            set @year = DATEPART(year, @date)
      end
end

select @date "DATE", @week "WEEK", @year "YEAR"

If anybody knew a more elegant way, that would be nice :-)

Solution

  • This solution The code in the question does not return the correct value for the date '1-1-2027'.

    The following will return the correct value with all dates i tested (and i tested quite a few).

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

    As taken from: https://capens.net/content/sql-year-iso-week