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
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
set
datefirst
3
print
dbo.fn_Calendar(
2007
,
12
)
select
dbo.fn_Calendar(
2007
,
12
)
set
datefirst
7
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
drop
function
dbo.fn_Calendar
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/*
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
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
------------------------------------------
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
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
(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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
return
end
go
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
set
datefirst
1
select
*
from
dbo.f_calendar(
2007
,
12
)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/*
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
drop
function
f_calendar
go
对比一下,感觉我的更容易理解,而且不管@@datefirst的值怎么变化都不会出错,libin_ftsafe的需要手动设置(set datefirst 1)。 另外,我的是直接返回一个串,libin_ftsafe返回的是一个table。