Construct date from year and week number in MSSQL
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);
Attention Read comments about DATEFIRST. This is depending on your culture...
According to my comment to your question this is a way to introduce such a running numbers table starting with 1900-01-01 up to somewhere in the year 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 bring you the current Monday:
SELECT * FROM dbo.RunningNumbers
WHERE CalendarYear = 2015
AND CalendarWeek = 37
AND CalendarWeekDay=1
part of the result
+--------+--------------+--------------+---------------+-------------+--------------+-----------------+-----------------+
| Number | CalendarDate | CalendarYear | CalendarMonth | CalendarDay | CalendarWeek | CalendarYearDay | CalendarWeekDay |
....
+--------+--------------+--------------+---------------+-------------+--------------+-----------------+-----------------+
| 43736 | 2019-09-30 | 2019 | 9 | 30 | 40 | 273 | 1 |
+--------+--------------+--------------+---------------+-------------+--------------+-----------------+-----------------+
| 43737 | 2019-10-01 | 2019 | 10 | 1 | 40 | 274 | 2 |
+--------+--------------+--------------+---------------+-------------+--------------+-----------------+-----------------+
| 43738 | 2019-10-02 | 2019 | 10 | 2 | 40 | 275 | 3 |
+--------+--------------+--------------+---------------+-------------+--------------+-----------------+-----------------+
....