-----自定义节假日
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO
--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null,--假日名称
Iswork int not null) --是否上班
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWorkDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetWorkDays]
GO
--计算两个日期之间的工作天数
CREATE FUNCTION GetWorkDays
(
@dt_begin DATETIME , --计算的开始日期
@dt_end DATETIME --计算的结束日期
)
RETURNS INT
AS
BEGIN
DECLARE @workday INT ,
@h_days INT ,
@w_days INT,
@i INT ,
@bz BIT ,
@dt DATETIME
IF @dt_begin > @dt_end
BEGIN
SELECT @bz = 1 ,
@dt = @dt_begin ,
@dt_begin = @dt_end ,
@dt_end = @dt
END
ELSE
BEGIN
SET @bz = 0
END
SELECT @h_days = count(*) from tb_Holiday where HDate BETWEEN @dt_begin and @dt_end and Iswork=0
SELECT @w_days = count(*) from tb_Holiday where HDate BETWEEN @dt_begin and @dt_end and Iswork=1
SELECT @i = DATEDIFF(Day, @dt_begin, @dt_end) + 1 ,
@workday = @i / 7 * 5 ,
@dt_begin = DATEADD(Day, @i / 7 * 7, @dt_begin)
WHILE @dt_begin <= @dt_end
BEGIN
SELECT @workday = CASE WHEN ( @@DATEFIRST + DATEPART(Weekday, @dt_begin) - 1 )
% 7 BETWEEN 1 AND 5 THEN @workday + 1
ELSE @workday
END ,
@dt_begin = @dt_begin + 1
END
RETURN(CASE WHEN @bz=1 THEN @h_days-@workday-@w_days-1 ELSE @workday-@h_days+@w_days END)
END
GO
