CODEWITHSUNDEEP
sqlsql-serverdatetimecalendardateadd
Nakia Downer
Nakia Downer

Reputation: 27

Create a calendar day table

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?

Upvotes: 0

Views: 159

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 1

Gaurav Rajput
Gaurav Rajput

Reputation: 647

Try below query, this will give you the required output:

DECLARE @STARTDATE DATETIME
SET @STARTDATE= CAST(MONTH(CURRENT_TIMESTAMP) AS     VARCHAR(100))+'/'+'01'+'/'+CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR(100))
;WITH MONTHDATA
AS
(SELECT @STARTDATE MONTHDATE
UNION ALL
SELECT DATEADD(D,1,MONTHDATE) FROM MONTHDATA WHERE     MONTHDATE<DATEADD(D,-1,DATEADD(M,1,@STARTDATE))
)
SELECT 'CD'+CAST( (ROW_NUMBER()OVER (ORDER BY MONTHDATE)) AS         VARCHAR(100))DATE_RULE,CONVERT(VARCHAR,MONTHDATE,101)MONTHDATE FROM MONTHDATA

OUTPUT

----------------------
DATE_RULE   MONTHDATE
----------------------
CD1 03/01/2017
CD2 03/02/2017
CD3 03/03/2017
.
.
.
CD29    03/29/2017
CD30    03/30/2017
CD31    03/31/2017
----------------------

Upvotes: 0

beejm
beejm

Reputation: 2481

DECLARE @startdate datetime = '2017-04-01'
DECLARE @startdate_for_loop datetime
SET @startdate_for_loop = @startdate

CREATE TABLE #T (date_rule nvarchar(100), exp_date datetime)
declare @x int = 1
WHILE MONTH(@startdate) = MONTH(@startdate_for_loop)
BEGIN
 INSERT INTO #T VALUES ('CD' + CAST(@x as nvarchar(max)), @startdate_for_loop)
 SET @x = @x + 1
 SET @startdate_for_loop = DATEADD(DD, 1, @startdate_for_loop)
END


SELECT * FROM #T

Upvotes: 0

Related Questions