Get correct week number for december 31st

Question

In SQL Server, I am trying to get the week number (with the year) of a given date, European style, so I'm using DATEPART with ISO_WEEK argument:

SELECT CAST(DATEPART(year, myDate) AS VARCHAR) + RIGHT('0' + CAST(DATEPART(ISO_WEEK, myDate) AS VARCHAR), 2);

This works well, except for December 31st of 2018, which falls on the 1st week of 2019, but since I'm using DATEPART for the year and the week separately, this obviously can't work. Here's an example when myDate is 31-12-2018:

SELECT CAST(DATEPART(year, '31-12-2018') AS VARCHAR) + RIGHT('0' + CAST(DATEPART(ISO_WEEK, '31-12-2018') AS VARCHAR), 2);

The above query returns '201801'.

Is there a way to simply get 201901 for December 31st 2018, without testing explicitly for this date ?

Possible duplicate of [SQL Get "ISO Year" for ISO Week](https://stackoverflow.com/questions/26926271/sql-get-iso-year-for-iso-week) — Diado, Jan 02 '19 at 14:11
What you want is `ISO_YEAR` which doesn't exist, so [I'd use this work around](https://stackoverflow.com/a/22829951/6167855) or similar logic — S3S, Jan 02 '19 at 14:13

score 2 · Accepted Answer · answered Jan 02 '19 at 14:33

See the possible duplicate reference and a little tidy up:

declare @d datetime = '2018-12-31';
select cast(year(dateadd(day, 26 - datepart(ISO_WEEK, @d), @d)) as varchar(4)) + right(('0' + cast(datepart(ISO_WEEK, @d) as varchar(2))), 2)

results in 201901.

Test code:

select cast(ds.d as date) as 'date', cast(year(dateadd(day, 26 - datepart(ISO_WEEK, ds.d), ds.d)) as varchar(4)) + right(('0' + cast(datepart(ISO_WEEK, ds.d) as varchar(2))), 2) as "IsoWeek"
from (
    select dateadd(day, x.num, '2018-12-30') as d
    from (
        select h.num + d.num + s.num as num
        from (
            select 0 num union select 100 num union select 200 num union select 300 num
        ) h
        cross join (
            select 0 num union select 10 num union select 20 num union select 30 num
        ) d
        cross join (
            select 0 num union select 1 num union select 2 union select 3 num
        ) s
    ) x    
) ds
order by ds.d

which results in:

date        IsoWeek
2018-12-30  201852
2018-12-31  201901
2019-01-01  201901
2019-01-02  201901
2019-01-09  201902
2019-01-10  201902
2019-01-11  201902
2019-01-12  201902
2019-01-19  201903
2019-01-20  201903
2019-01-21  201904
2019-01-22  201904
2019-01-29  201905
2019-01-30  201905
2019-01-31  201905
2019-02-01  201905
2019-04-09  201915
2019-04-10  201915
2019-04-11  201915
2019-04-12  201915
2019-04-19  201916
2019-04-20  201916
2019-04-21  201916
2019-04-22  201917
2019-04-29  201918
2019-04-30  201918
2019-05-01  201918
2019-05-02  201918
2019-05-09  201919
2019-05-10  201919
2019-05-11  201919
2019-05-12  201919
2019-07-18  201929
2019-07-19  201929
2019-07-20  201929
2019-07-21  201929
2019-07-28  201930
2019-07-29  201931
2019-07-30  201931
2019-07-31  201931
2019-08-07  201932
2019-08-08  201932
2019-08-09  201932
2019-08-10  201932
2019-08-17  201933
2019-08-18  201933
2019-08-19  201934
2019-08-20  201934
2019-10-26  201943
2019-10-27  201943
2019-10-28  201944
2019-10-29  201944
2019-11-05  201945
2019-11-06  201945
2019-11-07  201945
2019-11-08  201945
2019-11-15  201946
2019-11-16  201946
2019-11-17  201946
2019-11-18  201947
2019-11-25  201948
2019-11-26  201948
2019-11-27  201948
2019-11-28  201948

@iMezouar The "magic" with 26-week number is an arithmetic hack to get the "iso year" (work it out on a piece of paper!). The rest is just string handling (eg `right('0'+ number, 2) ` just zero pads the number: when the week is greater than 9 there will be a three char string. Summary: break it down sub-expression sub-expression. The test code is mostly generating dates by generating a selection of numbers and then adding them to a base date (mostly an excuse to use `cross join` :-) ). — Richard, Jan 02 '19 at 15:05
in case he doesn't get chance to tell you how it works (26 - WeekNumber) will be negative if weekNumber is 53. In which case for a date such as 01/01/2027, days will be subtracted making the year 2026. For 31/12/2026 days will also be subtracted leaving the year still in 2026. For 2018-12-31 you will end up adding 27 days, making the year 2018. It seems that you can never add or subtract too many days at the wrong time and go into the wrong year, which is pretty ingenious really. — Cato, Jan 02 '19 at 15:12
@Richard, My question was really about the (26 - week number) part which Cato gracefully explained, I've already had the concatenation part in my original query, and the cross join was clear enough :) Thanks to all. — Ibrahim Mezouar, Jan 02 '19 at 16:22

Cato · Answer 2 · 2019-01-02T14:53:21.023

the problem is that there is a unique case where the week number can be very 'early' in the year, although it is actually month 12, therefore it is easy to check if there is a start of year week in December, and in that case simply add one to the year,

DECLARE @mydate as datetime2 = '20270101';
SELECT CAST(DATEPART(year, @myDate) 
            + case when DATEPART(ISO_WEEK, @myDate) < 2 and month(@mydate) =12 then 
                                                      1 
                                                 else 
                                                       0 
                                                 end   

             + case when DATEPART(ISO_WEEK, @myDate) >10 and month(@mydate) =1 then 
                                                  -1 
                                             else 
                                                   0 
                                             end 
AS VARCHAR) 
              + RIGHT('0' + CAST(DATEPART(ISO_WEEK, @myDate) AS VARCHAR), 2);

I added a correction for the 2027 problem highlighted in another answer.

score 0 · Answer 3 · answered Jan 02 '19 at 14:31

Check following query, its using last day of the week to get the year value

SELECT CAST(DATEPART(year, DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, '2018-12-31'), DATEDIFF(dd, 0, '2018-12-31')))) AS VARCHAR) + RIGHT('0' + CAST(DATEPART(ISO_WEEK, '2018-12-31') AS VARCHAR), 2);

Get correct week number for december 31st

3 Answers3