Create a calendar day table

Question

I am trying to write a code where i can plug in a date and my table below will populate the expected date table with all the date for the particular month from CD1(Calendar Day 1) all the way to CD30 or CD31 or in February case CD28. I know i should begin my code with something like

Declare @startdate as datetime
Set @startdate = '20170401'

But after that I get confused with the DateAdd and DatePart code to create this query to produce the results

date rule  | expected date | 
----------------------------
| CD1      | 4/1/2017      |  
| CD2      | 4/2/2017      | 
| CD3      | 4/3/2017      |   
| CD4      | 4/4/2017      |   
| CD5      | 4/5/2017      | 
| CD6      | 4/6/2017      | 

Can anyone provide any assistance?

Answer 1

Try this,

Declare @startdate as datetime
Set @startdate = '20170401'

;with cte as
(
select @startdate dt,1 ruleid
union ALL
select dateadd(day,1,dt)
,ruleid+1
from cte    
where 
dt<dateadd(day,-1,dateadd(month, datediff(month,0,@startdate)+1,0))
)

select *,'CD'+cast(ruleid as varchar) CalenderRule

 from cte