Code Project

Link Unit

Sunday, March 09, 2008

Using SQL Server to get Calendar

I was going through this excellent blog http://blog.sqlauthority.com/ and seen the method used by the author Pinal Dave. I tried to do the same using another method. Hope visitors like it.

Create a sequence Table

Select identity(int,1,1) seq into Numbers from sysobjects s , sysobjects so

declare @d datetime

declare @d1 datetime

Set @d1=getdate()-10

set @d= dateadd(d,-day(@d1) + 1,@d1 )

Select @d
Select Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=1 then @d+N.seq-1 else null end),106),'') Monday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=2 then @d+N.seq-1 else null end),106),'')Tuesday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=3 then @d+N.seq-1 else null end),106),'') Wednesday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=4 then @d+N.seq-1 else null end),106),'') Thursday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=5 then @d+N.seq-1 else null end),106),'') Friday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=6 then @d+N.seq-1 else null end),106),'') Saturday,

Isnull(Convert(varchar(12),Min(Case When datepart(dw,@d+N.seq-1)=7 then @d+N.seq-1 else null end),106),'') Sunday

From Numbers N where seq between 1 and 31 and month(@d+N.seq-1) = month(@d)

Group by DatePart(wk,@d+N.seq-1)


Post a Comment