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.

Unipolar

More information needed - depends on where week 1 ends, and whether it has to be a full week or not. – Foolproof

ISO week ends on Sunday and the first week always contains Jan 4th. – Ameline

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.

Dreamadreamer

Side-note. I've played around with creating such calendar table for another question here. But the disadvantage is that the lookup for week 1 & 53 needs extra conditions. – Dreamadreamer

Answer 2

SQL Server has the DATEADD functionality that should help...

DECLARE @date_string NCHAR(6)
SELECT  @date_string = N'200852'

SELECT DATEADD(
           WEEK,
           CAST(RIGHT(@date_string, 2) AS INT),
           DATEADD(
               YEAR,
               CAST(LEFT(@date_string, 4) AS INT) - 1900,
               0
           )
       )

Once you have the value, use DATEPART to get what day of the week it is, and subtract that from your answer...

DECLARE @new_date DATETIME
SELECT  @new_date = '2008 Dec 30'

SELECT DATEADD(DAY, 1-DATEPART(dw, @new_date), @new_date)

This will bring the value to the start of the week, depending on what you have set DATEFIRST to.

Styles

Answer 3

Here's a solution that's almost database independent, if that's important.

Create a table called ALMANAC, primary key DATE, that lists all the attributes of a date that you plan on using. One of them could be YEAR, another could be WEEK. You can include whatever computable functions of DATE you want to use a lot. You could also include company specific DATE attributes, like whether the date is a company holiday.

Write one program to populate DATE for say 10 years (about 3650 rows plus some leap years). This program will use the environment specific date functions available to you either in the DBMS of in some programming language. Once you've populated the ALMANAC table, use it like ordinary data. Join the primary key with any Date fields in your database.

This turns out to be enormously useful for doing such things as preparing a report by week, by month, by quarter, and by year, almost automatically.

If DATE isn't fine enough granularity, you can even divide the Almanac into smaller units of time, like shifts where 3 shifts is one day.

Bateau

Why not 200 years or 500 years instead of 10 years? Otherwise, it may soon become December 33rd. – Babbler

@PeterMortensen, good question. It depends on the requirements. – Bateau

Answer 4

Date functions are pretty much database-specific. In MySQL, for example, you could do the following to get the Monday of the week in 'YYYYWW' format:

select date_sub(makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7), 
interval weekday(makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7)) DAY);

With makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7) you would get a date which is definitely in the given week and year, and with the date_sub function we go back to the Monday of that week.

Examples:

• for '200906' this would return '2009-02-02
• for '200901' it would return '2008-12-29' (this is in fact the Monday of week 1 of 2009)

Seawards

Answer 5

In Oracle:

SELECT  TO_DATE('04.01.' || SUBSTR(iso, 1, 4)) + (TO_NUMBER(SUBSTR(iso, 5, 2))) * 7 - TO_CHAR(TO_DATE('04.01.' || SUBSTR(iso, 1, 4)), 'D')
FROM    (
    SELECT  '200101' AS iso
    FROM    dual
    )

Ameline

Answer 6

If this is in Oracle, check out the TO_Date function with various options, for example, "YYYYWW":

select TO_Date ('200852' YYYYWW) from dual;

Ref.: Oracle date format

Once you get the beginning of the week, you can always add the number of days to get the weekend date.

Godgiven

Why the weird capitalisation "TO_Date"? Isn't it to_date? – Babbler