Get Week Start Date from Week Number and Year in SQL

Question

I have a week number and year and wanted the first day of the Week to start from Monday.

I also wanted to exclude the week if the week start date for the previous year date, for example, 31st Dec 2018 falls in the same week as of 1st Jan 2019, so in this case, I wanted Week Start Date from 7th Jan (Monday), 2019 for the year 2019.

Currently, I am trying this SQL code to get Week Start Date from Monday

DECLARE @y NVARCHAR(100) = '2019', 
        @w INT = 1;

SELECT 
  [WeekStartDate] = DATEADD(wk,DATEDIFF(wk,7,@y) + (@w),7);

  --2019-01-07 00:00:00.000 -- returns this date which is correct.

I get the correct Week Start Date until the year 2022 but when I try the year 2023, 2024 things go wrong. I want the week start date for year = 2023 and week = 1 to start from '2023-01-02' (Monday) and for the year 2024 and week = 1 to start from '2024-01-01' (Monday) and so on for upcoming years but I get different results as shown and explained below.

  DECLARE @y NVARCHAR(100) = '2023', 
        @w INT = 1;

SELECT 
  [WeekStartDate] = DATEADD(wk,DATEDIFF(wk,7,@y) + (@w),7);

  --2023-01-09 00:00:00.000 - returns this date which is incorrect 
  --2023-01-02 00:00:00.000 - I want this date to be Week Start Date, not 2023-01-09
    

DECLARE @y NVARCHAR(100) = '2024', 
        @w INT = 1;

SELECT 
  [WeekStartDate] = DATEADD(wk,DATEDIFF(wk,7,@y) + (@w),7);

  --2024-01-08 00:00:00.000, - returns this date which is incorrect
  --2024-01-01 00:00:00.000 - I want this date to be Week Start Date, not 2024-01-08

Any quick help to get this working for the year 2023, 2024, and other such cases so that it is consistent across all the years?

Any help is really appreciated. Thank you in advance.

SteveC SteveC · Accepted Answer · 2020-08-25T13:10:14

I think you can accomplish what you're looking for by converting the date to it's week offset and then converting back to a date. The conversion to week depends on the weekday of the starting date.

For 2019

declare 
  @yr           int=2019;
declare
  @dt           date=datefromparts(@yr, 1, 1),
  @sys_wk       int,
  @dt_from_wk   date;

select @sys_wk=datediff(wk, 0, @dt);
select cast(dateadd(wk, @sys_wk+1, 0) as date) yr_wk_start;

Results

yr_wk_start
2019-01-07

For 2023

declare 
  @yr           int=2023;
declare
  @dt           date=datefromparts(@yr, 1, 1),
  @sys_wk       int,
  @dt_from_wk   date;

select @sys_wk=datediff(wk, 0, @dt);
select cast(dateadd(wk, @sys_wk+1, 0) as date) yr_wk_start;

Results

yr_wk_start
2023-01-09

It works for 2024 as well...

Get Week Start Date from Week Number and Year in SQL

1 Answers