计算两个工作日前后的日期 [英] count working days b/w two dates

问题描述

我有一个代码可以计算两个日期之间的工作日，它不包括星期六和星期日作为假期，但是我只想排除星期日作为假期，请帮助我紧急.

这是排除周日和周六的代码

i have a code to count working days between two dates, it exclude saturdays and sundays as a holiday but i want to exclude only sundays as a holiday please help me its urgent.

here is code for excluding sunday''s and saturdays

ALTER FUNCTION [dbo].[GetWorkingDaysBWDates]
(
    @dt1 datetime,
    @dt2 datetime
)
RETURNS decimal
AS
BEGIN

    declare @Result int

    declare @WeekEnd int
    declare @wdf int
    declare @wdl int
    declare @fullWeek int

    set  @Result = 0
    select  @WeekEnd =  DATEPART ( dw , '02-04-2012')

    if(@dt1 > @dt2)
    begin
        RETURN @Result
    end


    select @wdf = @WeekEnd -  DATEPART ( dw , @dt1)

    if(@wdf > 5)
    begin
    set @wdf = 5
    end

    select @wdl =  DATEPART ( dw , @dt2)

    if(@wdl > 5)
    begin
    set @wdl = 5
    end

    Select @dt1 =  DATEADD(day,@WeekEnd - DATEPART (dw , @dt1)+1  ,@dt1)

    Select @dt2 =  DATEADD(day,-1 * DATEPART (dw , @dt2) + 1 ,@dt2)

    select  @fullWeek= (datediff(day, @dt1, @dt2))/7

    select @Result = (@fullWeek * 5) + @wdf + @wdl -1

    RETURN @Result

END

推荐答案

Try

Try

if(@wdl > 6)
    begin
    set @wdl = 6
    end

在处理星期几计算时，重要的是要考虑当前的DATEFIRST设置.此查询将始终正确排除周末，使用@@ DATEFIRST来说明一周中第一天的任何可能设置.

When dealing with day-of-week calculations, it''s important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

这篇关于计算两个工作日前后的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！