1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| DECLARE @Year INT;
DECLARE @Week INT;
-- Exemple 2
SELECT @Year = 2015
, @Week = 16
-----------------------
DECLARE @DateDebutAnneeExt DATETIME;
DECLARE @DateFinAnneeExt DATETIME;
SELECT @DateDebutAnneeExt = DATEADD(Week, -1, DATEADD(yyyy, @Year - 1900, 0))
SELECT @DateFinAnneeExt = DATEADD(Week, 1, DATEADD(yyyy, @Year - 1900 + 1, 0))
;WITH CTE AS (
SELECT 1 AS DateId
, @DateDebutAnneeExt AS DateCalend
, DATENAME(dw, @DateDebutAnneeExt) AS Jour
UNION ALL
SELECT CTE.DateId + 1 AS DateId
, DATEADD(d, 1, CTE.DateCalend)
, DATENAME(dw, DATEADD(d, 1 ,CTE.DateCalend)) AS Jour
FROM CTE
WHERE DATEADD(d,1,CTE.DateCalend) < @DateFinAnneeExt
)
SELECT
Jour
, DateCalend
, DATEPART(WEEK, DateCalend) AS Semaine
, DATEPART(year, DateCalend) As Annee
FROM CTE
WHERE DATEPART(year, DateCalend) = @year
AND DATEPART(Week, DateCalend) = @Week
OPTION (MaxRecursion 500) |
Partager