Beyondrelational

Saturday, March 27, 2010

Get Day, Month, Years Separatly

WITH CTE AS
(
SELECT CONVERT(DATETIME,'01/01/2010') AS dt, 
DATENAME(dw,CONVERT(DATETIME,'01/01/2010')) AS dy,
DATENAME(mm,CONVERT(DATETIME,'01/01/2010')) AS mn, 
YEAR(CONVERT(DATETIME,'01/01/2010')) AS yr
UNION ALL
SELECT s.dt+3 AS dts, 
DATENAME(dw,dt+3) AS dy ,
DATENAME(mm,dt+3) AS dy, 
YEAR(dt+3) AS yr
FROM cte s
WHERE s.dt
)
SELECT MAX(dt) AS dt,dy,mn,yr 
FROM cte
GROUP BY dy,mn,yr
UNION
SELECT MIN(dt) AS dt,dy,mn,yr 
FROM cte
GROUP BY dy,mn,yr
ORDER BY dt
OPTION (MaxRecursion 0)

No comments:

Post a Comment