SQL 计算两个日期之间的工作日
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/252519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count work days between two dates
提问by Ovidiu Pacurar
How can I calculate the number of work days between two dates in SQL Server?
如何计算 SQL Server 中两个日期之间的工作日数？
Monday to Friday and it must be T-SQL.
周一到周五，必须是T-SQL。
回答by CMS
For workdays, Monday to Friday, you can do it with a single SELECT, like this:
对于工作日，周一到周五，您可以使用一个 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)
If you want to include holidays, you have to work it out a bit...
如果你想包括假期，你必须努力解决......
回答by Bogdan Maxim
In Calculating Work Daysyou can find a good article about this subject, but as you can see it is not that advanced.
在计算工作日中，您可以找到一篇关于此主题的好文章，但正如您所见，它并不先进。
--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
If you need to use a custom calendar, you might need to add some checks and some parameters. Hopefully it will provide a good starting point.
如果您需要使用自定义日历，则可能需要添加一些检查和一些参数。希望它能提供一个很好的起点。
回答by Dan B
All Credit to Bogdan Maxim & Peter Mortensen. This is their post, I just added holidays to the function (This assumes you have a table "tblHolidays" with a datetime field "HolDate".
全部归功于 Bogdan Maxim 和 Peter Mortensen。这是他们的帖子，我只是在函数中添加了假期（假设您有一个带有日期时间字段“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)
*/
回答by AliceF
Another approach to calculating working days is to use a?WHILE?loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday – Friday. The complete script for calculating working days using the?WHILE?loop is shown below:
计算工作日的另一种方法是使用?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
Although the?WHILE?loop option is cleaner and uses less lines of code, it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years.
尽管?WHILE?loop 选项更简洁并且使用的代码行更少，但它有可能成为您环境中的性能瓶颈，尤其是当您的日期范围跨越几年时。
You can see more methods on how to calculate work days and hours in this article: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
你可以在这篇文章中看到更多关于如何计算工作日和小时数的方法：https: //www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
回答by Carter Cole
My version of the accepted answer as a function using DATEPART, so I don't have to do a string comparison on the line with
我将接受的答案作为函数使用的版本DATEPART，所以我不必在行上进行字符串比较
DATENAME(dw, @StartDate) = 'Sunday'
Anyway, here's my business datediff function
无论如何，这是我的业务 datediff 功能
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
回答by phareim
(I'm a few points shy of commenting privileges)
（我有点害羞评论特权）
If you decide to forgo the +1 day in CMS's elegant solution, note that if your start date and end date are in the same weekend, you get a negative answer. Ie., 2008/10/26 to 2008/10/26 returns -1.
如果您决定放弃CMS 优雅解决方案中的 +1 天，请注意，如果您的开始日期和结束日期在同一个周末，您会得到否定的答案。即，2008/10/26 到 2008/10/26 返回 -1。
my rather simplistic solution:
我相当简单的解决方案：
select @Result = (..CMS's answer..)
if (@Result < 0)
select @Result = 0
RETURN @Result
.. which also sets all erroneous posts with start dateafter end dateto zero. Something you may or may not be looking for.
.. 它还将开始日期在结束日期之后的所有错误帖子设置为零。您可能会或可能不会寻找的东西。
回答by joaopintocruz
For difference between dates including holidays I went this way:
对于包括假期在内的日期之间的差异，我是这样做的：
1) Table with Holidays:
1) 节假日表：
CREATE TABLE [dbo].[Holiday](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL)
2) I had my plannings Table like this and wanted to fill column Work_Days which was empty:
2）我有这样的计划表，想填充空的 Work_Days 列：
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) So in order to get "Work_Days" to later fill in my column just had to:
3) 所以为了让“Work_Days”稍后填写我的专栏，只需要：
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
Hope that I could help.
希望我能帮上忙。
Cheers
干杯
回答by Muthuvel
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
回答by user2733766
Here is a version that works well (I think). Holiday table contains Holiday_date columns that contains holidays your company observe.
这是一个运行良好的版本（我认为）。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
回答by adrianm
I know this is an old question but I needed a formula for workdays excluding the start date since I have several items and need the days to accumulate correctly.
我知道这是一个老问题，但我需要一个不包括开始日期的工作日公式，因为我有几个项目并且需要正确累积天数。
None of the non-iterative answers worked for me.
没有一个非迭代的答案对我有用。
I used a defintion like
我使用了一个像
Number of times midnight to monday, tuesday, wednesday, thursday and friday is passed
午夜到星期一、星期二、星期三、星期四和星期五经过的次数
(others might count midnight to saturday instead of monday)
（其他人可能会从午夜数到星期六而不是星期一）
I ended up with this formula
我最终得到了这个公式
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 */
