Q&A about the site for Tridion developers and administrators
SET DATEFIRST 1; WITH mycte AS ( SELECT DATEADD(dd, 0, '2019-01-01') DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 < '2020-12-31' ) SELECT datepart(year, dateadd(dd, -datepart(dw, DateValue) + 1, DateValue)) as TheYear, (datepart(week, dateadd(dd, - datepart(dw, dateadd(dd, - datepart(dw, DateValue), DateValue)) + 1, dateadd(dd, - datepart(dw, DateValue), DateValue))) - datepart(week, DateValue)) as diff, datepart(month, DateValue) as TheMonth, CASE WHEN datepart(week, DateValue) != 1 AND datepart(week, dateadd(dd, - datepart(dw, CONCAT(datepart(year, DateValue), '-01-01')) + 1, CONCAT(datepart(year, DateValue), '-01-01'))) in (52,53) AND CONCAT(datepart(year, DateValue), '-01-01') >= '2019-01-01' and CONCAT(datepart(year, DateValue), '-01-01') < '2020-12-31' AND (datepart(week, dateadd(dd, - datepart(dw, dateadd(dd, - datepart(dw, DateValue), DateValue)) + 1, dateadd(dd, - datepart(dw, DateValue), DateValue))) - datepart(week, DateValue)) != 1 THEN datepart(week, DateValue) -1 ELSE datepart(week, dateadd(dd, - datepart(dw, DateValue) + 1, DateValue)) END as TheWeek, DateValue as TheDate FROM mycte OPTION (MAXRECURSION 0);