在SQL2005下实现很方便,这里用的是CTE和Pivot,方法如下:
Use
Test
go
Set Nocount On
Declare
@Date datetime ,
@StartDate datetime ,
@EndDate datetime ,
@FirstIndex int
Set @Date = ' 20080222 ' -- 输入一个日期,即可算出当月的日历
Select
@StartDate = Convert ( char ( 6 ), @Date , 112 ) + ' 01 ' ,
@EndDate = Dateadd ( month , 1 , @StartDate ) - 1 ,
@FirstIndex = Datediff ( day , - 1 , @StartDate ) % 7
; With t As
(
Select Date = Convert ( int , 1 ),Row = ( @FirstIndex ) / 7 ,Col = @FirstIndex
Union All
Select Date = Date + 1 ,Row = ( @FirstIndex + Date) / 7 ,Col = (Date + @FirstIndex ) % 7
From t
Where Date <= Datediff ( day , @StartDate , @EndDate )
)
Select
[ 日 ] = Isnull ( Convert ( char ( 2 ), [ 0 ] ), '' ),
[ 一 ] = Isnull ( Convert ( char ( 2 ), [ 1 ] ), '' ),
[ 二 ] = Isnull ( Convert ( char ( 2 ), [ 2 ] ), '' ),
[ 三 ] = Isnull ( Convert ( char ( 2 ), [ 3 ] ), '' ),
[ 四 ] = Isnull ( Convert ( char ( 2 ), [ 4 ] ), '' ),
[ 五 ] = Isnull ( Convert ( char ( 2 ), [ 5 ] ), '' ),
[ 六 ] = Isnull ( Convert ( char ( 2 ), [ 6 ] ), '' )
From t
Pivot ( Max (Date) For col In ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] , [ 6 ] )) b
/*
2008年2月份
------------
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29
*/
go
Set Nocount On
Declare
@Date datetime ,
@StartDate datetime ,
@EndDate datetime ,
@FirstIndex int
Set @Date = ' 20080222 ' -- 输入一个日期,即可算出当月的日历
Select
@StartDate = Convert ( char ( 6 ), @Date , 112 ) + ' 01 ' ,
@EndDate = Dateadd ( month , 1 , @StartDate ) - 1 ,
@FirstIndex = Datediff ( day , - 1 , @StartDate ) % 7
; With t As
(
Select Date = Convert ( int , 1 ),Row = ( @FirstIndex ) / 7 ,Col = @FirstIndex
Union All
Select Date = Date + 1 ,Row = ( @FirstIndex + Date) / 7 ,Col = (Date + @FirstIndex ) % 7
From t
Where Date <= Datediff ( day , @StartDate , @EndDate )
)
Select
[ 日 ] = Isnull ( Convert ( char ( 2 ), [ 0 ] ), '' ),
[ 一 ] = Isnull ( Convert ( char ( 2 ), [ 1 ] ), '' ),
[ 二 ] = Isnull ( Convert ( char ( 2 ), [ 2 ] ), '' ),
[ 三 ] = Isnull ( Convert ( char ( 2 ), [ 3 ] ), '' ),
[ 四 ] = Isnull ( Convert ( char ( 2 ), [ 4 ] ), '' ),
[ 五 ] = Isnull ( Convert ( char ( 2 ), [ 5 ] ), '' ),
[ 六 ] = Isnull ( Convert ( char ( 2 ), [ 6 ] ), '' )
From t
Pivot ( Max (Date) For col In ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] , [ 6 ] )) b
/*
2008年2月份
------------
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29
*/
2006年写的一个,虽然能实现,但很土,o(∩_∩)o...:
/*
功能:绘画日历
设计:OK_008
时间:2006-05
*/
DECLARE @Year nvarchar ( 4 )
DECLARE @YearMonth nvarchar ( 7 ) -- 月份
DECLARE @strTop nvarchar ( 200 )
DECLARE @ForI INT , @ForYear INT , @MaxDay INT
DECLARE @RowX INT -- 行位置
DECLARE @strWeekDayList nvarchar ( 20 )
DECLARE @strPrint nvarchar ( 300 )
-- ======================================
SET @Year = ' 2006 ' -- 请在这里输入年份
-- ======================================
SET @strTop = ' 日 ' + char ( 9 ) + ' 一 ' + char ( 9 ) + ' 二 ' + char ( 9 ) + ' 三 ' ++ char ( 9 ) + ' 四 ' ++ char ( 9 ) + ' 五 ' ++ char ( 9 ) + ' 六 ' + char ( 13 ) +
' ─────────────────────────── '
SET @strWeekDayList = ' 日一二三四五六 '
SET @ForYear = 1
WHILE @ForYear <= 12 -- 1月份至12月份
BEGIN
SET @YearMonth = @Year + ' - ' + CAST ( @ForYear AS nvarchar ( 2 ))
SET @MaxDay = DAY ( DATEADD ( Day , - 1 , DATEADD ( Month , 1 , @YearMonth + ' -01 ' )))
SET @RowX = CHARINDEX ( RIGHT ( DATENAME (WeekDay, @YearMonth + ' -01 ' ), 1 ), @strWeekDayList ) - 1
SET @strPrint = ''
SET @ForI = 1
WHILE @ForI <= @RowX -- 构造1号的位置
BEGIN
SET @strPrint = @strPrint + CHAR ( 9 )
SET @ForI = @ForI + 1
END
SET @ForI = 1
WHILE @ForI <= @MaxDay -- 构造2号到月底的位置
BEGIN
SET @strPrint = @strPrint + CAST ( @ForI AS nvarchar ( 2 )) + Char ( 9 )
SET @RowX = @RowX + 1
SET @ForI = @ForI + 1
IF ( @RowX % 7 = 0 )
BEGIN
SET @RowX = 0
SET @strPrint = @strPrint + CHAR ( 13 )
END
END
SET @ForYear = @ForYear + 1
-- 打印输出一个月的结果
PRINT ' ━━━━━━━━━━━━━━━━━━━━━━━━━━━ '
PRINT + Char ( 9 ) ++ Char ( 9 ) + ' ' + @YearMonth + CHAR ( 10 )
PRINT @strTop
PRINT @strPrint + CHAR ( 10 )
END
功能:绘画日历
设计:OK_008
时间:2006-05
*/
DECLARE @Year nvarchar ( 4 )
DECLARE @YearMonth nvarchar ( 7 ) -- 月份
DECLARE @strTop nvarchar ( 200 )
DECLARE @ForI INT , @ForYear INT , @MaxDay INT
DECLARE @RowX INT -- 行位置
DECLARE @strWeekDayList nvarchar ( 20 )
DECLARE @strPrint nvarchar ( 300 )
-- ======================================
SET @Year = ' 2006 ' -- 请在这里输入年份
-- ======================================
SET @strTop = ' 日 ' + char ( 9 ) + ' 一 ' + char ( 9 ) + ' 二 ' + char ( 9 ) + ' 三 ' ++ char ( 9 ) + ' 四 ' ++ char ( 9 ) + ' 五 ' ++ char ( 9 ) + ' 六 ' + char ( 13 ) +
' ─────────────────────────── '
SET @strWeekDayList = ' 日一二三四五六 '
SET @ForYear = 1
WHILE @ForYear <= 12 -- 1月份至12月份
BEGIN
SET @YearMonth = @Year + ' - ' + CAST ( @ForYear AS nvarchar ( 2 ))
SET @MaxDay = DAY ( DATEADD ( Day , - 1 , DATEADD ( Month , 1 , @YearMonth + ' -01 ' )))
SET @RowX = CHARINDEX ( RIGHT ( DATENAME (WeekDay, @YearMonth + ' -01 ' ), 1 ), @strWeekDayList ) - 1
SET @strPrint = ''
SET @ForI = 1
WHILE @ForI <= @RowX -- 构造1号的位置
BEGIN
SET @strPrint = @strPrint + CHAR ( 9 )
SET @ForI = @ForI + 1
END
SET @ForI = 1
WHILE @ForI <= @MaxDay -- 构造2号到月底的位置
BEGIN
SET @strPrint = @strPrint + CAST ( @ForI AS nvarchar ( 2 )) + Char ( 9 )
SET @RowX = @RowX + 1
SET @ForI = @ForI + 1
IF ( @RowX % 7 = 0 )
BEGIN
SET @RowX = 0
SET @strPrint = @strPrint + CHAR ( 13 )
END
END
SET @ForYear = @ForYear + 1
-- 打印输出一个月的结果
PRINT ' ━━━━━━━━━━━━━━━━━━━━━━━━━━━ '
PRINT + Char ( 9 ) ++ Char ( 9 ) + ' ' + @YearMonth + CHAR ( 10 )
PRINT @strTop
PRINT @strPrint + CHAR ( 10 )
END