Write a code in Sql Server without using loop or cursor to generate a Calander Date Table

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 2662
Well we all know the importance of a Calender Date Table. One very important use of it is to find out the missing dates. It can be generated in many ways but here we will look into as how we can do so in a pure set base way.

Approach 1: Using the master..spt_values system table

DECLARE @startDate DATE='01/01/2012' -- mm/dd/yyyy
DECLARE @endDate DATE='12/31/2012' -- mm/dd/yyyy

[Date] = DATEADD(Day,Number,@startDate)
,[Month] = DATENAME(month,DATEADD(Day,Number,@startDate))
,[Year] = YEAR(DATEADD(Day,Number,@startDate) )
FROM master..spt_values
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate

If we do a
select * from master..spt_values WHERE Type='P'

we will get a number table. Using this fact we are adding one day to the DateAdd function until the EndDate specified is reached.

Approach 2: Using a recursive CTE

DECLARE @startDate DATETIME='01/01/2012' -- mm/dd/yyyy
DECLARE @endDate DATETIME='12/31/2012' -- mm/dd/yyyy

;WITH Calender AS (
SELECT @startDate AS CalanderDate
SELECT CalanderDate + 1 FROM Calender
WHERE CalanderDate + 1 <= @endDate


[Date] = CONVERT(VARCHAR(10),CalanderDate,101)
,[Month] = DATENAME(month,CalanderDate)
,[Year] = YEAR(CalanderDate)
FROM Calender

This program also is very similar to the first one but here we are adding 1 to every date generated in the recursive part. For this case , the output will a 1 year date starting for 1st January 2012 to 31st December 2012.

Hope this helps.

Comments or Responses

Login to post response