----功能:日历
----时间: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