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

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

SELECT 	
		[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
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)

SELECT 

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

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.