| 楼主： |
近日在一小项目中遇到需要计算今天之前（之后）若干工作日的问题，花数小时百度，均未搜到到满意答案，逐自行写了一个，颇感满意：
CREATE TABLE [dbo]. [T_SYS_Holiday](
[id] [int] IDENTITY(1, 1) NOT NULL,
[name] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Hdate] [datetime] NOT NULL,
[isholiday] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_T_SYS_Holiday] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)ON [PRIMARY]
GO
--2017年节假日数据
INSERT INTO T_SYS_Holiday
(name,Hdate,isholiday)
select '元旦补休','2017-01-02','节假日'
UNION select '春节补班','2017-01-22','非节假日'
UNION select '春节休息','2017-01-27','节假日'
UNION select '春节休息','2017-01-30','节假日'
UNION select '春节休息','2017-01-31','节假日'
UNION select '春节休息','2017-02-01','节假日'
UNION select '春节休息','2017-02-02','节假日'
UNION select '春节补班','2017-02-04','非节假日'
UNION select '清明补班','2017-04-01','非节假日'
UNION select '清明','2017-04-03','节假日'
UNION select '清明','2017-04-04','节假日'
UNION select '五一','2017-05-01','节假日'
UNION select '端午补班','2017-05-27','非节假日'
UNION select '端午','2017-05-29','节假日'
UNION select '端午','2017-05-30','节假日'
UNION select '国庆补班','2017-09-30','非节假日'
UNION select '国庆','2017-10-02','节假日'
UNION select '国庆','2017-10-03','节假日'
UNION select '国庆','2017-10-04','节假日'
UNION select '国庆','2017-10-05','节假日'
UNION select '国庆','2017-10-06','节假日'
---计算几天前的工作日
create function dbo.GetPreWorkDay
(@date0 datetime,
@x int)
returns datetime
as
begin
declare @i int,@date1 datetime
select @i=1,@date1=@date0
while(@i<=@x)
begin
select @date1=dateadd(d,-1,@date1)
if datepart(dw,@date1) not in(1,7)
begin
select @i=@i+1
end
if exists (select HDate from t_sys_holiday where isholiday='节假日' and @date1>= HDate and @date1<HDate +1)
begin
select @i=@i-1
end
if exists (select HDate from t_sys_holiday where isholiday='非节假日' and @date1>= HDate and @date1<HDate +1)
begin
select @i=@i+1
end
end
return @date1
end
Go
---计算几天后的工作日
create function dbo.GetNextWorkDay
(@date0 datetime,
@x int)
returns datetime
as
begin
declare @i int,@date1 datetime
select @i=1,@date1=@date0
while(@i<=@x)
begin
select @date1=dateadd(d,1,@date1)
if datepart(dw,@date1) not in(1,7)
begin
select @i=@i+1
end
if exists (select HDate from t_sys_holiday where isholiday='节假日' and @date1>= HDate and @date1<HDate +1)
begin
select @i=@i-1
end
if exists (select HDate from t_sys_holiday where isholiday='非节假日' and @date1>= HDate and @date1<HDate +1)
begin
select @i=@i+1
end
end
return @date1
end
Go
--select dbo.GetNextWorkDay('2017-01-20',7)
--select dbo.GetPreWorkDay('2017-02-4',7)
--select dbo.GetNextWorkDay('2017-04-20',7)
--select dbo.GetPreWorkDay('2017-05-2',7)
若有不当之处，望大伙指正。
----------------------------------------------
- |
