Get all days in a month


  
  
 
 DECLARE @month TINYINT
SET @month = 10;

DECLARE @year INT
SET @year = 2016;

--get the beginning of the passing month and year
declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)

---get beginning of next month 
DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  

;WITH mycte
     AS (SELECT @fromDate AS dt
         UNION ALL
         SELECT dt + 1
         FROM   mycte
         WHERE  dt + 1 < @toDate)

  SELECT replace(convert(CHAR(11),Dt, 106), ' ', '-') as SelDate FROM mycte

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s