create
function
fn_Calendar(
@year
int
,
@month
int
)
returns nvarchar ( max )
as
begin
declare @result nvarchar ( max ), @Enter nvarchar ( 8 )
select @Enter = char ( 13 ) + char ( 10 ), @result = ' Sun Mon Tue Wed Thu Fri Sat ' + @Enter -- 表头
declare @start datetime , @end datetime
select @start = rtrim ( @year ) + ' - ' + rtrim ( @month ) + ' -1 ' , @end = dateadd (mm, 1 , @start )
set @result = @result + replicate ( ' ' , ( datepart (dw, @start ) + @@datefirst + 6 ) % 7 ) -- 第一行前面的空格
while datediff (d, @start , @end ) > 0
begin
if ( datepart (dw, @start ) + @@datefirst ) % 7 = 1
select @result = @result + @Enter -- 是否换行
select @result = @result + right ( ' ' + rtrim ( day ( @start )), 4 ), @start = dateadd (d, 1 , @start )
end
return @result
end
go
set datefirst 3
print dbo.fn_Calendar( 2007 , 12 )
select dbo.fn_Calendar( 2007 , 12 )
set datefirst 7
drop function dbo.fn_Calendar
/**/ /*
Sun Mon Tue Wed Thu Fri Sat
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
30 31
------------------------------------------
Sun Mon Tue Wed Thu Fri Sat
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
30 31
(1 row(s) affected)
*/
returns nvarchar ( max )
as
begin
declare @result nvarchar ( max ), @Enter nvarchar ( 8 )
select @Enter = char ( 13 ) + char ( 10 ), @result = ' Sun Mon Tue Wed Thu Fri Sat ' + @Enter -- 表头
declare @start datetime , @end datetime
select @start = rtrim ( @year ) + ' - ' + rtrim ( @month ) + ' -1 ' , @end = dateadd (mm, 1 , @start )
set @result = @result + replicate ( ' ' , ( datepart (dw, @start ) + @@datefirst + 6 ) % 7 ) -- 第一行前面的空格
while datediff (d, @start , @end ) > 0
begin
if ( datepart (dw, @start ) + @@datefirst ) % 7 = 1
select @result = @result + @Enter -- 是否换行
select @result = @result + right ( ' ' + rtrim ( day ( @start )), 4 ), @start = dateadd (d, 1 , @start )
end
return @result
end
go
set datefirst 3
print dbo.fn_Calendar( 2007 , 12 )
select dbo.fn_Calendar( 2007 , 12 )
set datefirst 7
drop function dbo.fn_Calendar
/**/ /*
Sun Mon Tue Wed Thu Fri Sat
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
30 31
------------------------------------------
Sun Mon Tue Wed Thu Fri Sat
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
30 31
(1 row(s) affected)
*/
呵呵,下面是libin_ftsafe的:
create
function
f_calendar(
@year
int
,
@month
int
)
returns @t table (日 varchar ( 4 ),一 varchar ( 4 ),二 varchar ( 4 ),三 varchar ( 4 ),四 varchar ( 4 ),五 varchar ( 4 ),六 varchar ( 4 ))
as
begin
declare @a table (id int identity ( 0 , 1 ),date datetime )
insert into @a (date)
select top 31 rtrim ( @year ) + ' - ' + rtrim ( @month ) + ' -1 ' from sysobjects
update @a set date = dateadd (dd,id,date)
insert into @t
select
max ( case datepart (dw,date) when 7 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 1 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 2 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 3 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 4 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 5 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 6 then rtrim ( day (date)) else '' end )
from
@a
where
month (date) = @month
group by
( case datepart (dw,date) when 7 then datepart (week,date) + 1 else datepart (week,date) end )
return
end
go
set datefirst 1
select * from dbo.f_calendar( 2007 , 12 )
/**/ /*
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
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
30 31
*/
go
drop function f_calendar
go
returns @t table (日 varchar ( 4 ),一 varchar ( 4 ),二 varchar ( 4 ),三 varchar ( 4 ),四 varchar ( 4 ),五 varchar ( 4 ),六 varchar ( 4 ))
as
begin
declare @a table (id int identity ( 0 , 1 ),date datetime )
insert into @a (date)
select top 31 rtrim ( @year ) + ' - ' + rtrim ( @month ) + ' -1 ' from sysobjects
update @a set date = dateadd (dd,id,date)
insert into @t
select
max ( case datepart (dw,date) when 7 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 1 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 2 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 3 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 4 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 5 then rtrim ( day (date)) else '' end ),
max ( case datepart (dw,date) when 6 then rtrim ( day (date)) else '' end )
from
@a
where
month (date) = @month
group by
( case datepart (dw,date) when 7 then datepart (week,date) + 1 else datepart (week,date) end )
return
end
go
set datefirst 1
select * from dbo.f_calendar( 2007 , 12 )
/**/ /*
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
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
30 31
*/
go
drop function f_calendar
go
对比一下,感觉我的更容易理解,而且不管@@datefirst的值怎么变化都不会出错,libin_ftsafe的需要手动设置(set datefirst 1)。 另外,我的是直接返回一个串,libin_ftsafe返回的是一个table。