SQL Get "ISO Year" for ISO Week

Question

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 :-)

Conformist

Don't forget to add a tag of your specific RDBMS to attract the right people to your question. – Venetic

You also need to handle the case where week 52 stretches into january (in that case your weekyear is one less than the current year). See my answer below. – Tjirebon

Answer 1

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

Odometer

Nilla nilla please do not edit the post by using a second account. – Janinejanis

I believe this answer DOES in fact work for 1-1-2027. The result gives me 2026, which is correct, because 1-1-2017 is a Friday, which means the ISO week would be the last week of 2026. – Baines

a bit clever. but it makes sense – Epidote

That 26 is giving me a headache. I kind of intrinsically get what's going on but I'm wondering what range of values works here. Why 26? And yes I can confirm for all my data this works. – Urania

It's also quite the coincidence that literally as I'm working on this - getdate() has rolled over to 12/30/19 which is a Monday and therefore week 1 of 2020 :-) Happy new year! – Urania

Really elegant solution. By shifting the day to take the year from forward 25 days or deduct 26 or 27 days it pushes the date into the correct year to extract the calendar year. – Gusta

I think this code does not really comply with ISO week numbering. It is stated that "Weeks start with Monday and end on Sunday. Each week's year is the Gregorian year in which the Thursday falls". But select YEAR(DATEADD(day, 26 - DATEPART(isoww, '2016-01-01'), '2016-01-01')) returns 2015. Which seems wrong to me because 2016-01-01 is a Thursday, therefore, 2016-01-01 is the 4th day of the 1st week of 2016. This code returns also 2015 for all dates between 2015-12-27 and 2016-01-03. But 2015-12-29 (monday) to 2016-01-04 (sunday) belong to the "2016-W01". – Possessed

@BeChillerToo 2016-01-01 is a FRIDAY not a Thursday (at least in the calendar windows 11 is presenting me) so 2016-01-01 is in fact part of the last ISO Week of 2015. – Passementerie

@Passementerie indeed! I think I was confused with 2015, the first day of 2015 is a Thursday. – Possessed

Answer 2

This is the most compact solution I could come up with:

CASE
    WHEN DATEPART(ISO_WEEK, @Date) > 50 AND MONTH(@Date) = 1 THEN YEAR(@Date) - 1
    WHEN DATEPART(ISO_WEEK, @Date) = 1 AND MONTH(@Date) = 12 THEN YEAR(@Date) + 1
    ELSE YEAR(@Date) END

Can be used directly inside a SELECT statement. Or you could consider creating a user-defined function that takes the @Date parameter as input and outputs the result of the case statement.

Tjirebon

It is possible to shorten this slightly by taking the YEAR(@Date) bit outside the CASE expression: CASE WHEN ... THEN -1 WHEN ... THEN +1 ELSE 0 END + YEAR(@Date). – Seaddon

This seems to be working. – Possessed

Answer 3

I think this solution is much more logical and easier to comprehend for ISO-8601.

"The first week of the year is the week containing the first Thursday." see ISO Week definition

So we need to deduct the weekday of the given date from Thursday and add this to that same date to get the year.

Adding 5 and then taking the modulus of 7 to move Sunday to the previous week.

declare @TestDate date = '20270101'

select year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 5) % 7, @TestDate))

This will result in 2026 which is correct. This will give the correct result for all dates I check between 1990-2100 using this:

declare @TestDate date = '19900101'
declare @Results as table
(
    TestDate    date,
    FirstDayofYear  varchar(20),
    ISOYear int,
    ISOWeek int
)

while (@TestDate < '21000201')
begin
    insert @Results(TestDate, FirstDayofYear, ISOYEar, ISOWeek)
    select @TestDate, datename(weekday, dateadd(day, datepart(day, @TestDate) * -1 +1, @TestDate)),
        year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 5) % 7, @TestDate)), datepart(ISOWK, @TestDate)
    
    set @TestDate = dateadd(day, 1, @Testdate)

    if(datepart(day, @TestDate) > 7)
    begin
        set @TestDate = dateadd(year, 1, dateadd(day, datepart(day, @TestDate) * -1 + 1, @TestDate))
    end
end

-- Show all results that are wrong:
select * from @Results 
where (ISOYear <> datepart(year, TestDate) and ISOWeek < 3)
or (ISOYear = datepart(year, TestDate) and ISOWeek >= 52)

Casper

Does not work for '2015-01-04' (a sunday), your code returns "2014", but 2015-01-04 is "2015-W01-7" – Possessed

I found and like this solution, which helped me, thank you. I noticed that it's possible to create an ISO day of week function using: datepart(weekday, dateadd(day, -1, @TestDate). Then it's: year(dateadd(day, 4 - IsoWeekDay(@TestDate), @TestDate) to get the year. – Adulate

Answer 4

I think Bart Vanseer solution is nice and simple, but off by 1. I believe it should be

select year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))

Try following to find a few places were it differs

    declare @TestDate date = '2000-01-01' 

    DECLARE @cnt INT = 0;
    DECLARE @cnt_total INT = 10000;

    WHILE @cnt < @cnt_total
    BEGIN
       SET @TestDate = dateadd(day,1, @TestDate)

       if year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 6) % 8, @TestDate)) <>
          year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))
       BEGIN
         select @TestDate, year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 6) % 8, @TestDate))
         select @TestDate, year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))
       END

       SET @cnt = @cnt + 1;
    END;

Alibi

I just checked, this seems to be working for 2015 to 2023, this should be the accepted answer IMO. – Possessed

Indeed, I was off by 1. I ran a test script for 1990-2100 and that fixed my error. – Casper

Answer 5

Inspired by the other answers, i came to the following solution:

declare @TestDate date = '20270101'

SELECT DATEPART(year, DATEADD(day, 4 - DATEPART(weekday, @TestDate), @TestDate))

Just get thursday of the current week to get the correct year.

Assuming DATEFIRST is set to Monday (1).

Edit: How this works

1. Determinate the day of the week for the given date using DATEPART(weekday, [date]) (e.g. 5 for example above)
2. Subtract this from 4 to get the difference in days to thursday (4 will only works when datefirst is set to 1, adjust to your datefirst or add logic - e.g. -1 for example)
3. Use DATEADD on current date with the difference from 2. to get the date of thursday
4. Finally just use DATEPART(year, [date]) on this date do determinate the iso-week.

Wethington

That doesn't work. It will give you the year thursday is in but the week might still be of the previous year. Edit: Ah, you're right. That thursday should be is what marks the first week. Has been some years. Still I feel like there is something wrong with your solution, might have to think about it – Raye

For explanation: Because of the fact that the iso week definition says: "The first week of the year is the week containing the first Thursday" thursday will ever be on the correct year. – Wethington

There are only seven possible cases: First day of the year is a monday, tuesday, wednesday, thursday, friday, saturday or sunday. On cases monday till thursday the week belongs to the new year including thursday. On cases friday till sunday, the week belongs to the old year, including thursday. – Wethington

Interestingly this is the only answer that mentions datefirst in any shape or form. Does that setting really matter? Surely ISO implies international standard and therefore your country's localization shouldn't matter. That's my instinct but I just want to know the correct answer. – Urania

The reason why DATEFIRST should be considered is because the DATEPART weekday depends on DATEFIRST. – Wethington

If your DATEFIRST is 7 (sunday), you have to subtract from 5 (instead of 4) to determine the thursday of the current week. So the above query would only work with DATEFIRST = 1, but could easily written to any other DATEFIRST. It would also be possible to take DATEFIRST into account, but will increase complexity. – Wethington

This code is plainly wrong. You can't assume a DATEFIRST setting. – Indescribable

The provided approach isn't inherently "wrong"; it's rather a premise. Datefirst is typically predetermined based on location in most systems. It's up to you to devise a solution for a potentially dynamic Datefirst, which isn't overly challenging. However, this doesn't alter the fact that the presented solution reliably and logically determines the ISO week. – Wethington

@GertArnold Before you boldly claim an answer to be 'plainly wrong,' how about actually reading the question first? The original poster clearly specified he is looking for an "easy way" and a solution tailored to the European context where DATEFIRST is typically Monday. I've pointed out this constraint, and it's entirely up to individuals to extend the logic to accommodate dynamic DATEFIRST settings. But this would no longer be the desired “easy way”. Asserting the answer is wrong is not just incorrect but simply ignorant, showcasing a failure to read or comprehend the question at all! – Wethington

Answer 6

Use datetrunc to get the start date of the iso_week, add 3 days, take the year

The definition of an iso_week is based on:

The first week of the year is the week containing the first Thursday.

With datetrunc (SQL Server 2022) we can express that much more clearly like this

DATEPART(year, DATEADD(day, 3, DATETRUNC(iso_week, <date_column> )))

Find the date the week begins (Monday), add 3 days (to get to Thursday) and then take the year.

---

Example:

So based on a date of 2020-01-01 (a Wednesday)

DATETRUNC(iso_week, '2020-1-1')    

return the actual date of the first day of the iso week:

2019-12-30 00:00:00.0000000

Then DATEPART(year, '2019-12-30') gives us 2019

---

I can actually remember this way!

And given that it is just a scalar calculation it doesn't seem to make much difference with other methods:

Urania

Answer 7

DECLARE @date DATETIME
SET @date='2014-12-29'
SELECT 
CASE --Covers logic for ISO week date system which is part of the ISO 8601 date and time standard.  Ref: https://en.wikipedia.org/wiki/ISO_week_date
  WHEN (DATEPART(ISO_WEEK,@date) = 53) AND (DATEPART(MONTH,@date) = 1)
    THEN CAST((DATEPART(YEAR, @date) - 1) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
  WHEN (DATEPART(ISO_WEEK,@date) = 1) AND (DATEPART(MONTH,@date) = 12)
    THEN CAST((DATEPART(YEAR,@date) + 1) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
  ELSE CAST(DATEPART(YEAR,@date) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
 END AS ISO_week

Huesman

Answer 8

For IsoYear, get the Thursday from IsoWeek, and then get the year.

Aksum