Welcome to EMC Consulting Blogs

In addition to the T-SQL calendar table , which I have posted seperately, I went the whole hog and figured

How would you perform this query on SQL 2000?

Got is to work using #temp table. Hope this helps someone as your great post helped me!

Thanks,

Roy

-------------------------------

declare @start datetime,

@end datetime

set @start = '1900-01-01'

set @end = '2200-01-01'

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]

,       datepart(dy, dateadd(dd,dy,@start)) [day of year]

,       datename(dw, dateadd(dd,dy,@start)) [day]

,       datepart(dw, dateadd(dd,dy,@start)-1) [day of week]

,       datepart(dd, dateadd(dd,dy,@start)) [day of month]

,       datepart(ww, dateadd(dd,dy,@start)) [week]

,       datepart(mm, dateadd(dd,dy,@start)) [month]

,       datename(mm, dateadd(dd,dy,@start)) [month]

,       datepart(qq, dateadd(dd,dy,@start)) [quarter]

,       datepart(yy, dateadd(dd,dy,@start)) [year]

from #temp

drop table #temp

Very Neat! Worked 110%

Good! Exact what I was looking for. Thanks for post!

OH WOW...SO freaking COOL!

Excellent, this is just what I was looking for - thanks

Its a pity that this doesn't work inside a Inline UDF because the OPTION keyword is not accepted there. You would need to use the OPTION outside of the UDF or you would be limited to the default MAXRECURSION value of 100.

Excellent, Nice logic!!! it's need full 4 every one who need calendar in sql!

thanks for posting!!! hv nice time ahead!

Good work!

It has worked as per my expectations..... great!!!!

That's awesome. Thank you!