DECLARE @year INT = 2014
DECLARE @Month INT = 1
;WITH mths AS(
SELECT 1 AS mth, DATENAME(WEEKDAY, cast(@year*100+@Month AS VARCHAR) + '01') as monthname,CONVERT(DATETIME, cast(@year*100+@Month AS VARCHAR) + '01') AS mnthDT
UNION ALL
SELECT mth+1 AS mth, DATENAME(WEEKDAY, cast(@year*100+@Month AS VARCHAR) + (CASE LEN(CAST((1+mth) as VARCHAR)) WHEN 1 THEN '0'+ CAST((1+mth) as VARCHAR) ELSE CAST((1+mth) as VARCHAR) END) ) AS monthname,
CONVERT(DATETIME,cast(@year*100+@Month AS VARCHAR) + (CASE LEN(CAST((1+mth) as VARCHAR)) WHEN 1 THEN '0'+ CAST((1+mth) as VARCHAR) ELSE CAST((1+mth) as VARCHAR) END)) AS mnthDT FROM mths WHERE mnthDT < (SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, mnthDT) + 1, 0)))
)
SELECT * FROM mths
No comments:
Post a Comment