Thursday, 26 June 2014

Get days list by Month in SQL



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