SET DATEFIRST 1; WITH mycte AS ( SELECT DATEADD(dd, 0, '...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Tridion Meta

Q&A about the site for Tridion developers and administrators

SET DATEFIRST 1;

WITH mycte AS
(
 SELECT DATEADD(dd, 0, '2020-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') >= '2020-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);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results