工作日处理函数(标准节假日).

工作日处理函数(标准节假日).sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDay] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDay ] GO -- 计算两个日期相差的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime , -- 计算的开始日期 @dt_end datetime -- 计算的结束日期 ) RETURNS int AS BEGIN DECLARE @workday int , @i int , @bz bit , @dt datetime IF @dt_begin > @dt_end SELECT @bz = 1 , @dt = @dt_begin , @dt_begin = @dt_end , @dt_end = @dt ELSE SET @bz = 0 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 - @workday ELSE @workday END ) END GO /* ================================================================= */ if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDayADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDayADD ] GO -- 在指定日期上,增加指定工作天数后的日期 CREATE FUNCTION f_WorkDayADD( @date datetime , -- 基础日期 @workday int -- 要增加的工作日数 ) RETURNS datetime AS BEGIN DECLARE @bz int -- 增加整周的天数 SELECT @bz = CASE WHEN @workday < 0 THEN - 1 ELSE 1 END , @date = DATEADD (Week, @workday / 5 , @date ) , @workday = @workday % 5 -- 增加不是整周的工作天数 WHILE @workday <> 0 SELECT @date = DATEADD ( Day , @bz , @date ), @workday = CASE WHEN ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7 BETWEEN 1 AND 5 THEN @workday - @bz ELSE @workday END -- 避免处理后的日期停留在非工作日上 WHILE ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7 in ( 0 , 6 ) SET @date = DATEADD ( Day , @bz , @date ) RETURN ( @date ) END

工作日处理函数(自定义节假日).sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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 ) -- 假日名称 GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDay] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDay ] GO -- 计算两个日期之间的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime , -- 计算的开始日期 @dt_end datetime -- 计算的结束日期 ) RETURNS int AS BEGIN IF @dt_begin > @dt_end RETURN ( DATEDIFF ( Day , @dt_begin , @dt_end ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_begin AND @dt_end )) RETURN ( - ( DATEDIFF ( Day , @dt_end , @dt_begin ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_end AND @dt_begin ))) END GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDayADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDayADD ] GO -- 在指定日期上增加工作天数 CREATE FUNCTION f_WorkDayADD( @date datetime , -- 基础日期 @workday int -- 要增加的工作日数 ) RETURNS datetime AS BEGIN IF @workday > 0 WHILE @workday > 0 SELECT @date = @date + @workday , @workday = count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday ELSE WHILE @workday < 0 SELECT @date = @date + @workday , @workday =- count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday RETURN ( @date ) END

计算工作时间的函数.sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> if exists ( select * from dbo.sysobjects where id = object_id (N ' [tb_worktime] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 ) drop table [ tb_worktime ] GO -- 定义工作时间表 CREATE TABLE tb_worktime( ID int identity ( 1 , 1 ) PRIMARY KEY , -- 序号 time_start smalldatetime , -- 工作的开始时间 time_end smalldatetime , -- 工作的结束时间 worktime AS DATEDIFF (Minute,time_start,time_end) -- 工作时数(分钟) ) GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkTime] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkTime ] GO -- 计算两个日期之间的工作时间 CREATE FUNCTION f_WorkTime( @date_begin datetime , -- 计算的开始时间 @date_end datetime -- 计算的结束时间 ) RETURNS int AS BEGIN DECLARE @worktime int IF DATEDIFF ( Day , @date_begin , @date_end ) = 0 SELECT @worktime = SUM ( DATEDIFF (Minute, CASE WHEN CONVERT ( VARCHAR , @date_begin , 108 ) > time_start THEN CONVERT ( VARCHAR , @date_begin , 108 ) ELSE time_start END , CASE WHEN CONVERT ( VARCHAR , @date_end , 108 ) < time_end THEN CONVERT ( VARCHAR , @date_end , 108 ) ELSE time_end END )) FROM tb_worktime WHERE time_end > CONVERT ( VARCHAR , @date_begin , 108 ) AND time_start < CONVERT ( VARCHAR , @date_end , 108 ) ELSE SET @worktime = ( SELECT SUM ( CASE WHEN CONVERT ( VARCHAR , @date_begin , 108 ) > time_start THEN DATEDIFF (Minute, CONVERT ( VARCHAR , @date_begin , 108 ),time_end) ELSE worktime END ) FROM tb_worktime WHERE time_end > CONVERT ( VARCHAR , @date_begin , 108 )) + ( SELECT SUM ( CASE WHEN CONVERT ( VARCHAR , @date_end , 108 ) < time_end THEN DATEDIFF (Minute,time_start, CONVERT ( VARCHAR , @date_end , 108 )) ELSE worktime END ) FROM tb_worktime WHERE time_start < CONVERT ( VARCHAR , @date_end , 108 )) + CASE WHEN DATEDIFF ( Day , @date_begin , @date_end ) > 1 THEN ( DATEDIFF ( Day , @date_begin , @date_end ) - 1 ) * ( SELECT SUM (worktime) FROM tb_worktime) ELSE 0 END RETURN ( @worktime ) END