--创建节假日表
IF NOT EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'Holiday' AND OBJECTPROPERTY(ID, 'ISTABLE') = 1)
BEGINCREATE TABLE Holiday(
IID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
HD_Date DATETIME, --日期
HD_CHNDate VARCHAR(20), --农历
HD_WeekName VARCHAR(10), --星期
HD_State VARCHAR(10), --状态
HD_Memo VARCHAR(500) --备注
)
END
GO
--2013年节假日表
INSERT INTO Holiday(HD_Date, HD_WeekName, HD_State, HD_Memo)
SELECT '2013-01-01', '星期二', '休息日', '元旦放3天假'
UNION SELECT '2013-01-02', '星期三', '休息日', ''
UNION SELECT '2013-01-03', '星期四', '休息日', ''
UNION SELECT '2013-01-05', '星期六', '工作日', ''
UNION SELECT '2013-01-06', '星期日', '工作日', ''
UNION SELECT '2013-02-09', '星期六', '休息日', '春节7天假'
UNION SELECT '2013-02-10', '星期日', '休息日', ''
UNION SELECT '2013-02-11', '星期一', '休息日', ''
UNION SELECT '2013-02-12', '星期二', '休息日', ''
UNION SELECT '2013-02-13', '星期三', '休息日', ''
UNION SELECT '2013-02-14', '星期四', '休息日', ''
UNION SELECT '2013-02-15', '星期五', '休息日', ''
UNION SELECT '2013-02-16', '星期六', '工作日', ''
UNION SELECT '2013-02-17', '星期日', '工作日', ''
UNION SELECT '2013-04-04', '星期四', '休息日', '清明3天假'
UNION SELECT '2013-04-05', '星期五', '休息日', ''
UNION SELECT '2013-04-06', '星期六', '休息日', ''
UNION SELECT '2013-04-07', '星期日', '工作日', ''
UNION SELECT '2013-04-27', '星期六', '工作日', '五一劳动节假日调整'
UNION SELECT '2013-04-28', '星期日', '工作日', ''
UNION SELECT '2013-04-29', '星期一', '休息日', ''
UNION SELECT '2013-04-30', '星期二', '休息日', ''
UNION SELECT '2013-05-01', '星期三', '休息日', ''
UNION SELECT '2013-06-08', '星期六', '工作日', '端午节假日调整'
UNION SELECT '2013-06-09', '星期日', '工作日', ''
UNION SELECT '2013-06-10', '星期一', '休息日', ''
UNION SELECT '2013-06-11', '星期二', '休息日', ''
UNION SELECT '2013-06-12', '星期三', '休息日', ''
UNION SELECT '2013-09-19', '星期四', '休息日', '中秋节假日调整'
UNION SELECT '2013-09-20', '星期五', '休息日', ''
UNION SELECT '2013-09-21', '星期六', '休息日', ''
UNION SELECT '2013-09-22', '星期日', '工作日', ''
UNION SELECT '2013-09-29', '星期日', '工作日', '国庆节假日调整'
UNION SELECT '2013-10-01', '星期二', '休息日', ''
UNION SELECT '2013-10-02', '星期三', '休息日', ''
UNION SELECT '2013-10-03', '星期四', '休息日', ''
UNION SELECT '2013-10-04', '星期五', '休息日', ''
UNION SELECT '2013-10-05', '星期六', '休息日', ''
UNION SELECT '2013-10-06', '星期日', '休息日', ''
UNION SELECT '2013-10-07', '星期一', '休息日', ''
UNION SELECT '2013-10-12', '星期六', '工作日', ''
GO
--工作日处理函数
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'fn_WorkDay' AND XTYPE IN (N'FN', N'IF', N'TF'))
DROP FUNCTION fn_WorkDay
GO
--计算两个日期相差的工作天数
CREATE FUNCTION fn_WorkDay(
@DBegin DATETIME, --计算的开始日期
@DEnd DATETIME, --计算的结束日期
@CalcHD BIT = 1 --计算节假日
)RETURNS INT
AS
BEGIN
DECLARE @WorkDay INT, --工作日数
@Flag BIT, --标志,标志开始日期与结束日期的大小所计算结果的正负数
@TmpDate DATETIME, --临时变量存储
@HDNum INT, --节假日中的工作日数,正数表示工作日数,负数标识休息日数
@i INT
SET @HDNum = 0
--交换日期,比较开始日期与结束日期,并正确的设置大小顺序
IF @DBegin>@DEnd
SELECT @Flag=1, @TmpDate = @DBegin, @DBegin = @DEnd, @DEnd = @TmpDate
ELSE
SELECT @Flag=0
--将开始日期存储在临时变量中,后续计算需要
SET @TmpDate = @DBegin
--计算标准休息日
SELECT @i = DATEDIFF(Day, @DBegin, @DEnd) + 1,
@WorkDay = @i / 7 * 5,
@DBegin = DATEADD(Day, @i / 7 * 7, @DBegin)
WHILE @DBegin <= @DEnd
BEGIN
SELECT @WorkDay = CASE
WHEN (@@DATEFIRST + DATEPART(Weekday, @DBegin) - 1) % 7 BETWEEN 1 AND 5
THEN @WorkDay + 1 ELSE @WorkDay END,
@DBegin = @DBegin + 1
END
--计算节假日中的假日数
IF @CalcHD = 1
BEGIN
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'Holiday' AND OBJECTPROPERTY(ID, 'ISTABLE') = 1)
BEGIN
SELECT @HDNum = SUM(CASE WHEN HD_State='工作日' THEN 1 ELSE -1 END) FROM Holiday
WHERE HD_Date BETWEEN @TmpDate AND @DEnd
AND NOT ((HD_WeekName = '星期六' OR HD_WeekName = '星期日') AND HD_State = '休息日')
SET @WorkDay = @WorkDay + @HDNum
END
END
RETURN @WorkDay --CASE WHEN @Flag = 1 THEN -@WorkDay ELSE @WorkDay END
END
GO
--调用示例
SELECT dbo.fn_WorkDay('2013-06-01', '2013-06-30', DEFAULT)