存储过程做的日历（原创）

----功能：日历

----时间：2005-4-28

----作者：Tracy.Chuang

Create Proc SP_Calendar

@Date SmallDateTime = NULL

As

Begin

Declare @Calendar Table

(Week TinyInt,

Sun Varchar(2),

Mon Varchar(2),

Tue Varchar(2),

Wed Varchar(2),

Thu Varchar(2),

Fri Varchar(2),

Sat Varchar(2))



Declare @StartDate SmallDateTime,@EndDate SmallDateTime

Set @StartDate =DateAdd(Month,DateDiff(Month,0,IsNull(@Date,GetDate())),0)

Set @EndDate =DateAdd(Month,DateDiff(Month,0,IsNull(@Date,GetDate())) + 1,0) - 1



While DateDiff(Day,@EndDate,@StartDate) <= 0

Begin

Insert Into @Calendar

Select  DatePart(Week,@StartDate) As Week,

(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 1 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Sun,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 2 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Mon,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 3 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Tue,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 4 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Wed,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 5 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Thu,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 6 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Fri,
(Case (DatePart(DW,@StartDate)+@@DateFirst)%7  When 0 Then Cast(Day(@StartDate) As Varchar(2)) Else '' End) As Sat

Set @StartDate = @StartDate + 1

End



Select Max(Sun) As Sun,Max(Mon) As Mon,Max(Tue) As Tue,Max(Wed) As Wed,Max(Thu) As Thu,Max(Fri) As Fri,Max(Sat) As Sat

 From @Calendar

Group By week

End