Retrieve Year and week number from date in SQL Server

Question

I am trying to fetch the year number and week number in the format yyyyww like 201901, 201905, 201911 etc and I am using this query to do it:

declare @BeginDate datetime

set @beginDate = '2019-02-07'

select  
     concat(datepart(year, @BeginDate), right('0' + convert(varchar, datepart(week, dateadd(day, -7 , @BeginDate))), 2)) 

This worked fine till 2018 December. But when I try this for the date 2019-01-07, it returned 201953 which is not correct. But for all other values like 2019-01-14 etc it's fetching the week number correctly.

So how can I fetch the year week number combination accurately all over the year

Answer 1

You're not considering the 'year week' definition:

According to Wikipedia: First year's week is the week containing the first Thursday of the year or either the week containing the 4th of January or either the week that begins between 29th of Dec. and 4th of Jan.

So if year begins with week number 52 or 53 that is the trailing of previous year's last week.

so you have:

set dateformat ymd 
set datefirst 1
declare @data as datetime
declare @year as int

set @data = cast('2017-01-01' as datetime)
set @year = case when datepart(iso_week,@data) >= 52 and month(@data) = 1 
                      then year(@data)-1 
                 when datepart(iso_week,@data) = 1 and month(@data) = 12 
                      then year(@data)+1
                      else year(@data) end

select concat (@year, right('0'+cast(datepart(iso_week,@data) as varchar(6)),2))

result of this is 201652

edit: updated to have always double-digit week