计算两个日期之间相差工作日代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】计算两个日期之间相差工作日

作者:无形 / 发布于2014/4/11/ 496

计算两个日期之间相差工作日

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sinmen
-- Create date: 2007-11-01
-- Description: 计算两个日期之间相差的工作日
-- =============================================
ALTER FUNCTION [dbo].[WorkDatediff]
(
@begin_date datetime,
@end_date datetime
)
RETURNS int
AS
BEGIN
DECLARE @return_date_quantity int
DECLARE @temp datetime
DECLARE @week_quantity int
DECLARE @day_quantity int
DECLARE @begin_day_of_week int
DECLARE @end_day_of_week int
DECLARE @add_begin_day_quantity int
DECLARE @add_end_day_quantity int
set @day_quantity = Datediff(d,@begin_date,@end_date)
--判断传入的开始日期是否比结束日期大
if @day_quantity < 0
 begin
 set @temp = @begin_date
 set @begin_date = @end_date
 set @end_date = @temp
 end
set @week_quantity = Abs(Datediff(ww,@begin_date,@end_date)) - 1
if @week_quantity < 0
 set @week_quantity = 0
set @begin_day_of_week = Datepart(dw,@begin_date) - 1
set @end_day_of_week = Datepart(dw,@end_date) - 1

set @add_begin_day_quantity = case
    when @begin_day_of_week > 5 then 0
    else 6 - @begin_day_of_week --(5 - @begin_day_of_week + 1)
    end
set @add_end_day_quantity = case
    when abs(@day_quantity) < 8 then 0
    when @end_day_of_week > 5 then 5
    else @end_day_of_week
    end
if @day_quantity = 0
 set @return_date_quantity = 0
else
 set @return_date_quantity = @week_quantity * 5 + @add_begin_day_quantity + @add_end_day_quantity
if @day_quantity < 0
 set @return_date_quantity = @return_date_quantity * -1

RETURN @return_date_quantity
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　相差工作日　

同语言下

可能有用的

无形贡献的其它代码(10)