How can I convert a week (200851) into a date (2008-12-27)?

Question

How could I convert a year-week (for example, 0852 or 200852) into a date (for example, 2008-12-31 or specifically a week-ending day, that is, Saturday 2008-12-27 or a week-beginning day, that is, 2008-12-21)?

Any day of the week-ending will do, Friday, Saturday, Sunday or Monday.

Answer 1

I've created a UDF for this purpose. It'll convert a YYYYWW string or number to a date.

CREATE FUNCTION dbo.GetDateFromYearWeek (
 @YearWeek VARCHAR(7) = '000101', -- default
 @WeekDay INT = 1, -- default
 @FirstWeekDayName VARCHAR(9) = 'mon' -- default
) RETURNS DATE
BEGIN

 IF @YearWeek = '000101'
   SET @YearWeek = CONCAT(DATEPART(year, GETDATE()), '-', DATEPART(week, GETDATE()));

 IF @YearWeek NOT LIKE '[0-9][0-9][0-9][0-9]%[0-9-][0-9]'
   RETURN NULL;

 IF @WeekDay < 1 OR @WeekDay > 7
   RETURN NULL;

 DECLARE @FirstWeekDay INT = CHARINDEX(LOWER(LEFT(@FirstWeekDayName,3)), '   montuewedthufrisatsun')/3;
 IF @FirstWeekDay = 0 -- not found in string
   SET @FirstWeekDay = @@DATEFIRST;

 DECLARE @Year INT = TRY_CAST(LEFT(@YearWeek, 4) AS INT);
 DECLARE @Week INT = ABS(TRY_CAST(RIGHT(@YearWeek, 2) AS INT));

 DECLARE @Date DATE = TRY_CAST(CONCAT(@Year,'-01-01') AS DATE);
 SET @Date = DATEADD(week, @Week-1, @Date);

 DECLARE @DowDiff INT = (6-@FirstWeekday+@@DATEFIRST+DATEPART(weekday,@Date))%7;
 SET @Date = DATEADD(day, -@DowDiff, @Date);
 SET @Date = DATEADD(day, @WeekDay-1, @Date);

 RETURN @Date;
END;

Example usage:

SELECT *
, [StartOfWeek_SundayFirst] = dbo.GetDateFromYearWeek(col, 1, 'sun')
, [StartOfWeek_MondayFirst] = dbo.GetDateFromYearWeek(col, 1, 'mon')

, [EndOfWeek_SundayFirst]   = dbo.GetDateFromYearWeek(col, 7, 'sunday')
, [EndOfWeek_MondayFirst]   = dbo.GetDateFromYearWeek(col, 7, 'monday')
FROM (VALUES (202201), (202202)) q(col)
ORDER BY 1;

col	StartOfWeek_SundayFirst	StartOfWeek_MondayFirst	EndOfWeek_SundayFirst	EndOfWeek_MondayFirst
202201	2021-12-26	2021-12-27	2022-01-01	2022-01-02
202202	2022-01-02	2022-01-03	2022-01-08	2022-01-09

Test it on the db<>fiddle here.

ISO_WEEK Version

CREATE FUNCTION dbo.GetDateFromIsoYearWeek (
 @YearWeek VARCHAR(7) = '0000-00', -- default
 @WeekDay INT = 1 -- default
) RETURNS DATE
BEGIN

 IF @YearWeek = '0000-00'
   SET @YearWeek = CONCAT(DATEPART(year, GETDATE()), '-', DATEPART(iso_week, GETDATE()));

 IF @YearWeek NOT LIKE '[0-9][0-9][0-9][0-9]%[0-9-][0-9]'
   RETURN NULL;

 IF @WeekDay < 1 OR @WeekDay > 7
   RETURN NULL;

 DECLARE @FirstWeekDay INT = 1; -- monday
 DECLARE @Year INT = TRY_CAST(LEFT(@YearWeek, 4) AS INT);
 DECLARE @Week INT = ABS(TRY_CAST(RIGHT(@YearWeek, 2) AS INT));

 DECLARE @Date DATE = TRY_CAST(CONCAT(@Year,'-01-08') AS DATE);
 SET @Date = DATEADD(week, @Week - 2 + (DATEPART(week, @Date)-(DATEPART(iso_week, @Date))), @Date);

 DECLARE @DowDiff INT = (6-@FirstWeekday+@@DATEFIRST+DATEPART(weekday,@Date))%7;
 SET @Date = DATEADD(day, -@DowDiff, @Date);
 SET @Date = DATEADD(day, @WeekDay-1, @Date);

 RETURN @Date;
END;

DECLARE @Test TABLE ([column] char(7));
INSERT INTO @Test VALUES
  ('2020-53'), ('2021-01'), ('2021-02')
, ('2021-48')
, ('2021-53'), ('2022-01'), ('2022-02')
;

SELECT [column]
, [FirstOfWeek] = dbo.GetDateFromIsoYearWeek([column], 1)
, [LastOfWeek]  = dbo.GetDateFromIsoYearWeek([column], 7)
FROM @Test
ORDER BY 1;

column	FirstOfWeek	LastOfWeek
2020-53	2020-12-28	2021-01-03
2021-01	2021-01-04	2021-01-10
2021-02	2021-01-11	2021-01-17
2021-48	2021-11-29	2021-12-05
2021-53	2022-01-03	2022-01-09
2022-01	2022-01-03	2022-01-09
2022-02	2022-01-10	2022-01-16

Test it on the db<>fiddle here.