Get Start and End Date from Week Number SQL Server-CodePudding

We have WeekNo and Year entered into a table in following format:

YYYY-ww     (2021-48)

I want start date and end date from given week no with year as above format.

How to get it from SQL query or procedure?

Thanks,

CodePudding user response：

How about this?

declare @dateText varchar(7);
declare @week int;
declare @year int;

set datefirst 1;              -- Set first day of week to Monday
set @dateText = '2021-48';
set @year = SUBSTRING(@dateText, 1, 4)
set @week = SUBSTRING(@dateText, 6, 2)

select DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4 -
     DATEPART(dw, DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4)   1

Output
-----------------
2021-11-29 00:00:00.000

Alternatively you could create a function:

CREATE FUNCTION dbo.GetStartOfWeek(@dateText varchar(7))
RETURNS Datetime
BEGIN
    
declare @week int;
declare @year int;
select @year = SUBSTRING(@dateText, 1, 4), @week = SUBSTRING(@dateText, 6, 2)

 RETURN DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4 -
        DATEPART(dw, DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4)   1
END;

Which you can use in a SQL query or stored procedure:

select dbo.GetStartOfWeek('2021-48') as StartDate, DATEADD(d, 7, dbo.GetStartOfWeek('2021-48')) as EndDate

CodePudding user response：

Here some test snippets to calculate the start & end day of a Year-Week in a query.

1) Lots of date/time functions

SET datefirst 1;
DECLARE @Test TABLE ([YEAR-WW] char(7)); 
INSERT INTO @Test VALUES 
('2021-01'), ('2021-02'), 
('2021-48'), 
('2021-53'), 
('2022-01'), ('2022-02');

SELECT 
  [YEAR-WW]
, [StartOfWeek] = CAST(DATEADD(day, 1-DATEPART(weekday, DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-07')), DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-07')) AS DATE)
, [EndOfWeek]   = CAST(DATEADD(day, 6, DATEADD(day, 1-DATEPART(weekday, DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-07')), DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-07'))) AS DATE)
FROM @Test
ORDER BY 1;

YEAR-WW | StartOfWeek | EndOfWeek 
:------ | :---------- | :---------
2021-01 | 2020-12-28  | 2021-01-03
2021-02 | 2021-01-04  | 2021-01-10
2021-48 | 2021-11-22  | 2021-11-28
2021-53 | 2021-12-27  | 2022-01-02
2022-01 | 2021-12-27  | 2022-01-02
2022-02 | 2022-01-03  | 2022-01-09

2) UDF GetDateFromYearWeek

CREATE FUNCTION dbo.GetDateFromYearWeek (
 @YearWeek VARCHAR(7) = '0000-00', 
 @WeekDay INT = 1
) RETURNS DATE
BEGIN
 
 IF @YearWeek = '0000-00'
   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 @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-07') AS DATE);
 SET @Date = DATEADD(week, @Week - 2, @Date);
 SET @Date = DATEADD(day, 1-DATEPART(weekday, @Date), @Date);
 SET @Date = DATEADD(day, @WeekDay-1, @Date);

 RETURN @Date;
END;

Demo on db<>fiddle here