Build date with year and week number in MSSQL

Question

If I have a YEAR and WEEK , what's the clean way to build a DATE from it? I would prefer the day of the week to be on Monday.

Answer 1

Use DATEADD

Rextester demo

 DECLARE @y INT = 2015, @w INT = 37; SELECT [StartOfWeek] = DATEADD(wk,DATEDIFF(wk,7,CAST(@y AS NVARCHAR(100))) + (@w-1),7); 

Answer 2

Caution Read comments about DATEFIRST . It depends on your culture ...

According to my comment on your question, this is a way to introduce such a table of launch numbers starting from 1900-01-01, sometime in 2173.

 CREATE TABLE dbo.RunningNumbers(Number INT NOT NULL ,CalendarDate DATE NOT NULL ,CalendarYear INT NOT NULL ,CalendarMonth INT NOT NULL ,CalendarDay INT NOT NULL ,CalendarWeek INT NOT NULL ,CalendarYearDay INT NOT NULL ,CalendarWeekDay INT NOT NULL); DECLARE @CountEntries INT = 100000; DECLARE @StartNumber INT = 0; WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1 E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows CteTally AS ( SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr FROM E8 ) INSERT INTO dbo.RunningNumbers SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.* FROM CteTally CROSS APPLY ( SELECT DATEADD(DAY,CteTally.Nmbr,{ts'1900-01-01 00:00:00'}) ) AS CalendarDate(d) CROSS APPLY ( SELECT YEAR(CalendarDate.d) AS CalendarYear ,MONTH(CalendarDate.d) AS CalendarMonth ,DAY(CalendarDate.d) AS CalendarDay ,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek ,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay ,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay ) AS CalendarExt; 

This will result in the current Monday:

 SELECT * FROM dbo.RunningNumbers WHERE CalendarYear = 2015 AND CalendarWeek = 37 AND CalendarWeekDay=1 

Hint: indexes must be specified!

Answer 3

I solved this with this query:

 dateadd(week, the_week_column-1, to_date(cast(the_year_column as string), 'YYYY')) 

Where

• to_date(cast(the_year_column as string), 'YYYY') returns the start date of the year, for example, 2018-01-01
• the_week_column - 1 indicates how many weeks to add to the start date.