Server中是實現日歷的幾個方法？
數據庫應該創建一個日歷表
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 CTE2create 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 @groupend,@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 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