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

Niladri.Biswas
Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 2185
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.

Comments or Responses

Login to post response