计算两次约会之间的工作日

如何计算 SQLServer 中两个日期之间的工作日数？

星期一到星期五，它必须是 T-SQL。

436869 次浏览
小开

在 计算工作日中你可以找到一篇关于这个主题的好文章，但是正如你所看到的，它并没有那么高级。

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
@StartDate DATETIME,
@EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)


--Define the output data type.
RETURNS INT


AS
--Calculate the RETURN of the function.
BEGIN
--Declare local variables
--Temporarily holds @EndDate during date reversal.
DECLARE @Swap DATETIME


--If the Start Date is null, return a NULL and exit.
IF @StartDate IS NULL
RETURN NULL


--If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
IF @EndDate IS NULL
SELECT @EndDate = @StartDate


--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
--Usually faster than CONVERT.
--0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
@EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)


--If the inputs are in the wrong order, reverse them.
IF @StartDate > @EndDate
SELECT @Swap      = @EndDate,
@EndDate   = @StartDate,
@StartDate = @Swap


--Calculate and return the number of workdays using the input parameters.
--This is the meat of the function.
--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate, @EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate, @EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
)
END
GO

如果需要使用自定义日历，则可能需要添加一些检查和一些参数。希望这将提供一个良好的起点。

小开
最佳答案

对于工作日，星期一到星期五，你可以使用一个 SELECT，像这样:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'




SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

如果你想把假期包括在内，你就得稍微..。

小开 
 DECLARE @TotalDays INT,@WorkDays INT
DECLARE @ReducedDayswithEndDate INT
DECLARE @WeekPart INT
DECLARE @DatePart INT


SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 2
ELSE 0 END
SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
SET @WeekPart=@TotalDays/7;
SET @DatePart=@TotalDays%7;
SET @WorkDays=(@WeekPart*5)+@DatePart


RETURN @WorkDays
小开 
DECLARE @StartDate datetime,@EndDate datetime


select @StartDate='3/2/2010', @EndDate='3/7/2010'


DECLARE @TotalDays INT,@WorkDays INT


DECLARE @ReducedDayswithEndDate INT


DECLARE @WeekPart INT


DECLARE @DatePart INT


SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1


SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 2
ELSE 0 END


SET @TotalDays=@TotalDays-@ReducedDayswithEndDate


SET @WeekPart=@TotalDays/7;


SET @DatePart=@TotalDays%7;


SET @WorkDays=(@WeekPart*5)+@DatePart


SELECT @WorkDays
小开

我使用 DATEPART将接受的答案作为一个函数，所以我不需要对

DATENAME(dw, @StartDate) = 'Sunday'

总之，这是我的商业约会功能

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION BDATEDIFF
(
@startdate as DATETIME,
@enddate as DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @res int


SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
-(DATEDIFF(wk, @startdate, @enddate) * 2)
-(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)


RETURN @res
END
GO
小开 
CREATE FUNCTION x
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Teller INT


SET @StartDate = DATEADD(dd,1,@StartDate)


SET @Teller = 0
IF DATEDIFF(dd,@StartDate,@EndDate) <= 0
BEGIN
SET @Teller = 0
END
ELSE
BEGIN
WHILE
DATEDIFF(dd,@StartDate,@EndDate) >= 0
BEGIN
IF DATEPART(dw,@StartDate) < 6
BEGIN
SET @Teller = @Teller + 1
END
SET @StartDate = DATEADD(dd,1,@StartDate)
END
END
RETURN @Teller
END
小开

(我离评论特权还差几分)

如果你决定放弃 CMS 的优雅解决方案中的 + 1天，请注意，如果你的开始日期和结束日期在同一个周末，你会得到一个否定的答案。也就是说，2008/10/26年度至2008/10/26年度的回报率为 -1。

我相当简单的解决方案:

select @Result = (..CMS's answer..)
if  (@Result < 0)
select @Result = 0
RETURN @Result

..它还将 截止日期之后 开始日期的所有错误帖子设置为零。你可能在找的东西。

小开

对于不同的日期，包括假期，我这样做:

1)假期表:

    CREATE TABLE [dbo].[Holiday](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL)

2)我的计划表是这样的，我想填写“工作日”这一栏，这一栏是空的:

    CREATE TABLE [dbo].[Plan_Phase](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Plan] [int] NOT NULL,
[Id_Phase] [int] NOT NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[Work_Days] [int] NULL)

3)因此，为了让“工作日”以后能够填写我的专栏，我只需要:

SELECT Start_Date, End_Date,
(DATEDIFF(dd, Start_Date, End_Date) + 1)
-(DATEDIFF(wk, Start_Date, End_Date) * 2)
-(SELECT COUNT(*) From Holiday Where Date  >= Start_Date AND Date <= End_Date)
-(CASE WHEN DATENAME(dw, Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, End_Date) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where Start_Date  = Date) > 0 THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where End_Date  = Date) > 0 THEN 1 ELSE 0 END) AS Work_Days
from Plan_Phase

希望我能帮上忙。

干杯

小开

我在这里举了各种例子，但是在我的特定情况下，我们有一个用于交付的@PromisedDate 和一个用于实际收到项目的@ReceivedDate。当一个项目收到之前的“承诺日期”的计算是不正确的，除非我命令的日期传递到函数的日历顺序。由于不想每次都检查日期，我更改了这个函数来处理这个问题。

Create FUNCTION [dbo].[fnGetBusinessDays]
(
@PromiseDate date,
@ReceivedDate date
)
RETURNS integer
AS
BEGIN
DECLARE @days integer


SELECT @days =
Case when @PromiseDate > @ReceivedDate Then
DATEDIFF(d,@PromiseDate,@ReceivedDate) +
ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2 +
CASE
WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1
ELSE 0
END +
(Select COUNT(*) FROM CompanyHolidays
WHERE HolidayDate BETWEEN @ReceivedDate AND @PromiseDate
AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
Else
DATEDIFF(d,@PromiseDate,@ReceivedDate)  -
ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2  -
CASE
WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1
ELSE 0
END -
(Select COUNT(*) FROM CompanyHolidays
WHERE HolidayDate BETWEEN @PromiseDate and @ReceivedDate
AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
End




RETURN (@days)


END
小开

这是一个很好的版本(我认为)。Holiday 表包含 Holiday _ date 列，其中包含公司观察到的假日。

DECLARE @RAWDAYS INT


SELECT @RAWDAYS =  DATEDIFF(day, @StartDate, @EndDate )--+1
-( 2 * DATEDIFF( week, @StartDate, @EndDate ) )
+ CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END


SELECT  @RAWDAYS - COUNT(*)
FROM HOLIDAY NumberOfBusinessDays
WHERE [Holiday_Date] BETWEEN @StartDate+1 AND @EndDate
小开

都归功于 Bogdan Maxim 和 Peter Mortensen。这是他们的帖子，我只是在函数中添加了假日(这里假设您有一个带有 datetime 字段“ HolDate”的表“ tblHolidays”。

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
AND XType IN (N'FN', N'IF', N'TF')
)


DROP FUNCTION [dbo].[fn_WorkDays]
GO
CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
@StartDate DATETIME,
@EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)


--Define the output data type.
RETURNS INT


AS
--Calculate the RETURN of the function.
BEGIN
--Declare local variables
--Temporarily holds @EndDate during date reversal.
DECLARE @Swap DATETIME


--If the Start Date is null, return a NULL and exit.
IF @StartDate IS NULL
RETURN NULL


--If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
IF @EndDate IS NULL
SELECT @EndDate = @StartDate


--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
--Usually faster than CONVERT.
--0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
@EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)


--If the inputs are in the wrong order, reverse them.
IF @StartDate > @EndDate
SELECT @Swap      = @EndDate,
@EndDate   = @StartDate,
@StartDate = @Swap


--Calculate and return the number of workdays using the input parameters.
--This is the meat of the function.
--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate, @EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate, @EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
--Subtract all holidays
-(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]
where  [HolDate] between @StartDate and @EndDate )
)
END
GO
-- Test Script
/*
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/
小开

如果需要将工作日添加到给定日期，可以创建一个依赖于日历表的函数，如下所述:

CREATE TABLE Calendar
(
dt SMALLDATETIME PRIMARY KEY,
IsWorkDay BIT
);


--fill the rows with normal days, weekends and holidays.




create function AddWorkingDays (@initialDate smalldatetime, @numberOfDays int)
returns smalldatetime as


begin
declare @result smalldatetime
set @result =
(
select t.dt from
(
select dt, ROW_NUMBER() over (order by dt) as daysAhead from calendar
where dt > @initialDate
and IsWorkDay = 1
) t
where t.daysAhead = @numberOfDays
)


return @result
end
小开

使用日期表:

    DECLARE
@StartDate date = '2014-01-01',
@EndDate date = '2014-01-31';
SELECT
COUNT(*) As NumberOfWeekDays
FROM dbo.Calendar
WHERE CalendarDate BETWEEN @StartDate AND @EndDate
AND IsWorkDay = 1;

如果没有，可以使用数字表:

    DECLARE
@StartDate datetime = '2014-01-01',
@EndDate datetime = '2014-01-31';
SELECT
SUM(CASE WHEN DATEPART(dw, DATEADD(dd, Number-1, @StartDate)) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) As NumberOfWeekDays
FROM dbo.Numbers
WHERE Number <= DATEDIFF(dd, @StartDate, @EndDate) + 1 -- Number table starts at 1, we want a 0 base

它们都应该是快速的，它消除了模糊性/复杂性。第一个选项是最好的，但是如果您没有日历表，您总是可以使用 CTE 创建一个数字表。

小开

这对我很有效，在我的国家，周六和周日是非工作日。

对我来说，重要的是@StartDate 和@EndDate 的时间。

CREATE FUNCTION [dbo].[fnGetCountWorkingBusinessDays]
(
@StartDate as DATETIME,
@EndDate as DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @res int


SET @StartDate = CASE
WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEADD(dd, 2, DATEDIFF(dd, 0, @StartDate))
WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN DATEADD(dd, 1, DATEDIFF(dd, 0, @StartDate))
ELSE @StartDate END


SET @EndDate = CASE
WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @EndDate))
WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd, -1, DATEDIFF(dd, 0, @EndDate))
ELSE @EndDate END




SET @res =
(DATEDIFF(hour, @StartDate, @EndDate) / 24)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)


SET @res = CASE WHEN @res < 0 THEN 0 ELSE @res END


RETURN @res
END


GO
小开

创建如下函数:

CREATE FUNCTION dbo.fn_WorkDays(@StartDate DATETIME, @EndDate DATETIME= NULL )
RETURNS INT
AS
BEGIN
DECLARE @Days int
SET @Days = 0


IF @EndDate = NULL
SET @EndDate = EOMONTH(@StartDate) --last date of the month


WHILE DATEDIFF(dd,@StartDate,@EndDate) >= 0
BEGIN
IF DATENAME(dw, @StartDate) <> 'Saturday'
and DATENAME(dw, @StartDate) <> 'Sunday'
and Not ((Day(@StartDate) = 1 And Month(@StartDate) = 1)) --New Year's Day.
and Not ((Day(@StartDate) = 4 And Month(@StartDate) = 7)) --Independence Day.
BEGIN
SET @Days = @Days + 1
END


SET @StartDate = DATEADD(dd,1,@StartDate)
END


RETURN  @Days
END

你可以这样调用函数:

select dbo.fn_WorkDays('1/1/2016', '9/25/2016')

或者说:

select dbo.fn_WorkDays(StartDate, EndDate)
from table1
小开

这基本上是 CMS 的答案，不依赖于特定的语言设置。而且因为我们的拍摄通用，这意味着它应该工作的所有 @@datefirst设置以及。

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
/* if start is a Sunday, adjust by -1 */
+ case when datepart(weekday, <start>) = 8 - @@datefirst then -1 else 0 end
/* if end is a Saturday, adjust by -1 */
+ case when datepart(weekday, <end>) = (13 - @@datefirst) % 7 + 1 then -1 else 0 end

datediff(week, ...)总是使用周六到周日的边界，因此表达式是确定性的，不需要修改(只要我们对工作日的定义始终是周一到周五)日期编号确实会根据 @@datefirst的设置而变化，修改后的计算处理了这个修正，但有一些同余关系的复杂性很小。

处理星期六/星期日事务的一种更清晰的方法是在提取星期值之前翻译日期。移位后，这些值将回归到一个固定的(可能更为熟悉的)数字，该数字从周日的1开始，到周六的7结束。

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
+ case when datepart(weekday, dateadd(day, @@datefirst, <start>)) = 1 then -1 else 0 end
+ case when datepart(weekday, dateadd(day, @@datefirst, <end>))   = 7 then -1 else 0 end

我追踪这种形式的解决方案至少可以追溯到2002年和 Itzik Ben-Gan 的一篇文章。(https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx)虽然它需要一个小的调整，因为新的 date类型不允许日期算术，它在其他方面是相同的。

编辑: 我把不知何故中断的 +1加了回去。同样值得注意的是，这种方法总是计算开始和结束的日子。它还假定结束日期在开始日期之上或之后。

小开 
Create Function dbo.DateDiff_WeekDays
(
@StartDate  DateTime,
@EndDate    DateTime
)
Returns Int
As


Begin


Declare @Result Int = 0


While   @StartDate <= @EndDate
Begin
If DateName(DW, @StartDate) not in ('Saturday','Sunday')
Begin
Set @Result = @Result +1
End
Set @StartDate = DateAdd(Day, +1, @StartDate)
End


Return @Result

完

小开

计算工作日的另一种方法是使用 WHILE 循环，该循环基本上遍历一个日期范围，每当发现日期在星期一至星期五之间时，就将其递增1。使用 WHILE 循环计算工作日的完整脚本如下:

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop]
(@DateFrom DATE,
@DateTo   DATE
)
RETURNS INT
AS
     BEGIN
         DECLARE @TotWorkingDays INT= 0;
         WHILE @DateFrom <= @DateTo
             BEGIN
                 IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
                     BEGIN
                         SET @TotWorkingDays = @TotWorkingDays + 1;
                 END;
                 SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
             END;
         RETURN @TotWorkingDays;
     END;
GO

尽管 WHILE 循环选项更简洁，使用的代码行更少，但它有可能成为环境中的性能瓶颈，特别是当日期范围跨越几年时。

您可以在本文中看到更多关于如何计算工作日和工作时间的方法: Https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

小开

我发现下面的 TSQL 是一个相当优雅的解决方案(我没有运行函数的权限)。我发现 DATEDIFF忽略了 DATEFIRST，我希望一周的第一天是星期一。我还希望第一个工作日设置为零，如果它落在周末，星期一将是一个零。这可以帮助那些需求稍有不同的人:)

它不处理银行假期

SET DATEFIRST 1
SELECT
,(DATEDIFF(DD,  [StartDate], [EndDate]))
-(DATEDIFF(wk,  [StartDate], [EndDate]))
-(DATEDIFF(wk, DATEADD(dd,-@@DATEFIRST,[StartDate]), DATEADD(dd,-@@DATEFIRST,[EndDate]))) AS [WorkingDays]
FROM /*Your Table*/
小开

一种方法是从头到尾“遍历日期”，并结合一个大小写表达式来检查这一天是否是星期六或星期天，然后标记出来(工作日为1，周末为0)。最后只需求和标志(它等于1-标志的计数，另一个标志是0)就可以得到工作日的天数。

您可以使用 GetNums (startNumber，endNumber)类型的实用函数，该函数生成一系列数字，用于从开始日期到结束日期的“循环”。有关实现，请参阅 http://tsql.solidq.com/SourceCodes/GetNums.txt。该逻辑还可以扩展以适应假日(比如说，如果您有一个假日表)

declare @date1 as datetime = '19900101'
declare @date2 as datetime = '19900120'


select  sum(case when DATENAME(DW,currentDate) not in ('Saturday', 'Sunday') then 1 else 0 end) as noOfWorkDays
from dbo.GetNums(0,DATEDIFF(day,@date1, @date2)-1) as Num
cross apply (select DATEADD(day,n,@date1)) as Dates(currentDate)
小开

与 DATEDIFF 一样，我不认为结束日期是间隔的一部分。 (例如)@StartDate 和@EndDate 之间的星期日数是“初始”星期一和@EndDate 之间的星期日数减去这个“初始”星期一和@StartDate 之间的星期日数。有了这些资料，我们可以计算出以下的工作天数:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/01/01'
SET @EndDate = '2019/01/01'


SELECT DATEDIFF(Day, @StartDate, @EndDate) -- Total Days
- (DATEDIFF(Day, 0, @EndDate)/7 - DATEDIFF(Day, 0, @StartDate)/7) -- Sundays
- (DATEDIFF(Day, -1, @EndDate)/7 - DATEDIFF(Day, -1, @StartDate)/7) -- Saturdays

最好的问候！

小开

我知道这是一个老问题，但我需要一个公式的工作日不包括开始日期，因为我有几个项目，需要天积累正确。

没有一个非迭代的答案适合我。

我用的定义是

午夜至星期一、星期二、星期三、星期四和星期五的次数已过

(其他人可能会把午夜算作星期六，而不是星期一)

我得到了这个公式

SELECT DATEDIFF(day, @StartDate, @EndDate) /* all midnights passed */
- DATEDIFF(week, @StartDate, @EndDate) /* remove sunday midnights */
- DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate)) /* remove saturday midnights */
小开

我从别人那里借鉴了一些想法来创造我的解决方案。我使用内联代码来忽略周末和美国联邦假日。在我的环境中，EndDate 可能为 null，但它永远不会出现在 StartDate 之前。

CREATE FUNCTION dbo.ufn_CalculateBusinessDays(
@StartDate DATE,
@EndDate DATE = NULL)


RETURNS INT
AS


BEGIN
DECLARE @TotalBusinessDays INT = 0;
DECLARE @TestDate DATE = @StartDate;




IF @EndDate IS NULL
RETURN NULL;


WHILE @TestDate < @EndDate
BEGIN
DECLARE @Month INT = DATEPART(MM, @TestDate);
DECLARE @Day INT = DATEPART(DD, @TestDate);
DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @TestDate) - 1; --Monday = 1, Tuesday = 2, etc.
DECLARE @DayOccurrence INT = (@Day - 1) / 7 + 1; --Nth day of month (3rd Monday, for example)


--Increment business day counter if not a weekend or holiday
SELECT @TotalBusinessDays += (
SELECT CASE
--Saturday OR Sunday
WHEN @DayOfWeek IN (6,7) THEN 0
--New Year's Day
WHEN @Month = 1 AND @Day = 1 THEN 0
--MLK Jr. Day
WHEN @Month = 1 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
--G. Washington's Birthday
WHEN @Month = 2 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
--Memorial Day
WHEN @Month = 5 AND @DayOfWeek = 1 AND @Day BETWEEN 25 AND 31 THEN 0
--Independence Day
WHEN @Month = 7 AND @Day = 4 THEN 0
--Labor Day
WHEN @Month = 9 AND @DayOfWeek = 1 AND @DayOccurrence = 1 THEN 0
--Columbus Day
WHEN @Month = 10 AND @DayOfWeek = 1 AND @DayOccurrence = 2 THEN 0
--Veterans Day
WHEN @Month = 11 AND @Day = 11 THEN 0
--Thanksgiving
WHEN @Month = 11 AND @DayOfWeek = 4 AND @DayOccurrence = 4 THEN 0
--Christmas
WHEN @Month = 12 AND @Day = 25 THEN 0
ELSE 1
END AS Result);


SET @TestDate = DATEADD(dd, 1, @TestDate);
END


RETURN @TotalBusinessDays;
END
小开

以上所有功能都不适用于同一周，也不适用于节假日。我写道:

create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
IF DATENAME( dw, @Date ) = 'Saturday'
SET @Date = DATEADD(day, - 1, @Date)


ELSE IF DATENAME( dw, @Date ) = 'Sunday'
SET @Date = DATEADD(day, 1, @Date)


RETURN @date
END
GO


create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
declare @s varchar(50)


SELECT @s = CASE
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = @date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = @date THEN 'Independence Day'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = @date THEN 'Christmas Day'
--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = @date THEN 'New Years Eve'
--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = @date THEN 'Veteran''s Day'


WHEN [Month] = 1  AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' THEN 'Martin Luther King Day'
WHEN [Month] = 5  AND [DayOfMonth] >= 25             AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 9  AND [DayOfMonth] <= 7              AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' THEN 'Day After Thanksgiving'
ELSE NULL END
FROM (
SELECT
[Year] = YEAR(@date),
[Month] = MONTH(@date),
[DayOfMonth] = DAY(@date),
[DayName]   = DATENAME(weekday,@date)
) c


RETURN @s
END
GO


create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE
AS
RETURN (
select dt, dbo.GetHoliday(dt) as Holiday
from (
select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
from master..spt_values
where type='p'
) d
where year(dt) = @year and dbo.GetHoliday(dt) is not null
)


create proc UpdateHolidaysTable
as


if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
create table Holidays(dt date primary key clustered, Holiday varchar(50))


declare @year int
set @year = 1990


while @year < year(GetDate()) + 20
begin
insert into Holidays(dt, Holiday)
select a.dt, a.Holiday
from dbo.GetHolidays(@year) a
left join Holidays b on b.dt = a.dt
where b.dt is null


set @year = @year + 1
end


create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT
AS
BEGIN
IF @StartDate IS NULL OR @EndDate IS NULL
RETURN  0


IF @StartDate >= @EndDate
RETURN  0


DECLARE @Days int
SET @Days = 0


IF year(@StartDate) * 100 + datepart(week, @StartDate) = year(@EndDate) * 100 + datepart(week, @EndDate)
--same week
select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
ELSE
--diff weeks
select @Days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
 

RETURN  @Days
END

提交答案