I was actually asked recently about a calendar table and I've never really been a big fan. Too much admin.

The request was very similar, a full list of days even if no sales. The analyst that asked said could he create a calendar table and do it that way with a join and I turned him down due to administration, so he asked for another result that would mean it could be dynamic, no matter what dates were put in, past or future.

So, I worked out a quick function to give a calendar result.

create function [dbo].[fn_callist](@startdate date , @enddate date)

returns @results table (caldate date,yearnum int,monthnum int,weeknum int,daynum int,

monname varchar(12),weekday varchar(20))

as

begin

declare @loop int = 0

declare @loopend int = 0

set @loopend=DATEDIFF(d,@startdate,@enddate)

while @loop<>@loopend+1

begin

insert into @results

select DATEADD(d,@loop,@startdate),0,0,0,0,'',''

set @loop=@loop+1

end

update @results

set yearnum=DATEPART(yy,caldate),

monthnum=datepart(m,caldate),

monname=DATENAME(MONTH,caldate),

weeknum=DATEPART(WEEK,caldate),

daynum=DATEPART(d,caldate),

weekday=DATENAME(WEEKDAY,caldate)

return

end

GO

It's pretty fast, does the same kind of thing as a table but needs no real admin and gives you full details of the date. Great for when they don't like you adding tables to a DB. Also makes life a little easier should you wish to add some new functionality to it.