Generating temporary calendar tables in SQL Server 2000 and 2005

Many a times, we get a question as to get all the days or sundays or weeks between two given dates and join it with someother table and get the result and the suggestion given in the discussion forum is to use a calendar table.

Why should I consider using an auxiliary calendar table?

Though I would say thats the best option to use, performance wise, if we have a lot of operations on dates, I would rather prefer to use my own small table generated for a specific requirement, use it and dispose it. Again, calendar table has got a lot of positives that this solution can never address. You can decide which one you want to use based on the alternatives I have given.

But before reading forward, I would suggest you look at this link on calendar tables (just for you to see both sides of the coin)

Now, lets say I want to get all the days between 2 given dates and then use it in a query. I can get all the days between these two days in SQL Server 2000, this way:

declare @start datetime,
@end datetime

set @start = '2006-01-01'
set @end = '2006-05-02'

declare @no_of_Days int
set @no_of_days = datediff(dd,@start,@end) + 1
set rowcount @no_of_days

select identity(int,0,1) as dy into #temp from sysobjects a, sysobjects b

set rowcount 0

select dateadd(dd,dy,@start) as [days] from #temp

drop table #temp

Again this can be extended by applying the dateparts function once we get the [days] column.

In SQL Server 2005, life is pretty simple. Most of what I get in the calendar table, except for the holidays, I can get it here. This is how I do it and you can check the result by executing the script :)

declare @start datetime,
@end datetime

set @start = '2006-01-01'
set @end = '2007-01-01'
;
with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as
(
select @start ,
case when datepart(dw,@start) in (1,7) then 0 else 1 end,
year(@start),
datepart(qq,@start),
datepart(mm,@start),
datepart(dd,@start),
datepart(dw,@start),
datename(month, @start),
datename(dw, @start),
datepart(wk, @start)
union all
select date + 1,
case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from calendar where date + 1< @end
)
select * from calendar option(maxrecursion 10000)

You can use the above script create your calendar table by using the select into clause.