Archive

Archive for August, 2012

Generating Calendar with T-SQL Query

In this thread, I would like to show some T-SQL Queries/Stored Procedures with which we can generate the calendar between two dates. Following one is the first example for initial kickoff, it’s a simple dates in between generator with provided the start and end date as the parameters,

Query:

create table #temp (dayidx date)
declare @startdate date, @Enddate date, @variable int, @counter int
set @startdate='07/01/2012'
set @Enddate=GETDATE()
set @variable=DATEDIFF(D,@startdate,@Enddate)
Set @counter =0
insert into #temp values (@startdate)
while (@counter!=@variable)
begin
Set @counter=@counter+1
insert into #temp values (DATEADD (DAY,@counter,@startdate))
end
select * from #temp
drop table #temp

Feed the values for @startdate and @Enddate, it will generate the dates in between. Enhancements can be done like adding day, dayname, weekno, monthname, monthnumber etc.

The same can be implemented using the following method which i read in some forum and sometimes it may be very useful to have an alternate method. Please see the query below which perform the same calendar generation with provided start and end dates.

declare @StartDate datetime, @EndDate datetime
set @StartDate = '08/01/2012'
set @EndDate = GETDATE()
;with Calendar as(
select @StartDate dateidx
union all
select dateidx + 1
from    Calendar
where   dateidx + 1 < @EndDate)
select @StartDate
from    Calendar
OPTION (MAXRECURSION 0)

Sample output:

image

 

Thanks 🙂

%d bloggers like this: