Beyondrelational

Saturday, March 27, 2010

Get Dates From The Particular Day Number

DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear = '2010-01-01'
SELECT DISTINCT DATEADD(d, number, @FirstDateOfYear),
CASE DATEPART(dw, DATEADD(d, number, @FirstDateOfYear))
WHEN 7 THEN 'Saturday'
WHEN 1 THEN 'Sunday'
ELSE 'Work Day'
END
FROM master..spt_values
WHERE number BETWEEN 0 AND 364
AND (DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 1 
OR DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 7)
ORDER BY DATEADD(d, number, @FirstDateOfYear)

No comments:

Post a Comment