- 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 The Wed Thu Fri Sta\' + @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 The Wed Thu Fri Sta
- 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 The Wed Thu Fri Sta
- 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)
- */
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create function F_month(@YMonth nvarchar(6))
returns @T table (日 varchar (4 ) ,一 varchar (4 ) ,二 varchar (4 ) ,三 varchar (4 ) ,四 varchar (4 ) ,五 varchar (4 ) ,六 varchar (4 ) )
as
begin
declare @Tmp table ( [weekday ] int , [day ] nvarchar (2 ) , [ group ] int ) ---增加一列作为分组显示
declare @i int , @j int , @ date datetime , @ group int
select @ date = @YMonth + \ '01\' , @i =datediff (dd , @ date ,dateadd (month ,1 , @ date ) ) , @j =0 , @ group =0
while @i > @j
begin
insert @Tmp select (datepart (dw , @ date ) + @ @datefirst -1 ) %7 ,datepart (d , @ date ) ,case when (datepart (dw , @ date ) + @ @datefirst -1 ) %7 =0 then @ group +1 else @ group end
select @j = @j +1 , @ group =case when (datepart (dw , @ date ) + @ @datefirst -1 ) %7 =0 then @ group +1 else @ group end , @ date =dateadd (dd ,1 , @ date )
end
insert @T
select
max (case when [weekday ] =0 then [day ] else \ '\' end ) ,
max (case when [weekday ] =1 then [day ] else \ '\' end ) ,
max (case when [weekday ] =2 then [day ] else \ '\' end ) ,
max (case when [weekday ] =3 then [day ] else \ '\' end ) ,
max (case when [weekday ] =4 then [day ] else \ '\' end ) ,
max (case when [weekday ] =5 then [day ] else \ '\' end ) ,
max (case when [weekday ] =6 then [day ] else \ '\' end )
from
@Tmp
group by [ group ]
return
end
go
select * from F_month ( \ '0712\' )
或 :
select * from F_month ( \ '200712\' )
数据库应该创建一个日历表
CREATE TABLE Calendar(
date datetime NOT NULL PRIMARY KEY CLUSTERED,
weeknum int NOT NULL,
weekday int NOT NULL,
weekday_desc nchar(3) NOT NULL,
is_workday bit NOT NULL,
is_weekend bit NOT NULL
)
GO
WITH CTE1 AS(
SELECT
date = DATEADD(day,n,'19991231')
FROM Nums
WHERE n <= DATEDIFF(day,'19991231','20201231')),
CTE2 AS(
SELECT
date,
weeknum = DATEPART(week,date),
weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7,
weekday_desc = DATENAME(weekday,date)
FROM CTE1)
--INSERT INTO Calendar
SELECT
date,
weeknum,
weekday,
weekday_desc,
is_workday = CASE WHEN weekday IN (0,6) THEN 0 ELSE 1 END,
is_weekend = CASE WHEN weekday IN (0,6) THEN 1 ELSE 0 END
FROM CTE2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22392018/viewspace-1068772/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22392018/viewspace-1068772/