Home > T-SQL > Generating Calendar with T-SQL Query

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 🙂

Advertisements
  1. camilo
    October 7, 2013 at 10:56 pm

    Excellent !!! thx a lot !!!

    Like

  2. Charles
    August 18, 2015 at 4:05 am

    Hey there. Thanks for the article. On the recursive calendar generator, I think you’ll need to change the final select statement to:

    select dateidx
    from Calendar
    OPTION (MAXRECURSION 0)

    … otherwise you’ll only get the result 2012-08-01.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: