如何计算SQL Server中两个日期之间的工作日数？
星期一到星期五,它必须是T-SQL.
对于周一至周五的工作日,您可以使用单个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)
如果你想包括假期,你必须稍微解决一下......
在计算工作日中,您可以找到关于此主题的好文章,但正如您所看到的那样,它并不是那么先进.
--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
如果您需要使用自定义日历,则可能需要添加一些检查和一些参数.希望它能提供一个良好的起点.
所有归功于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)
*/
计算工作日的另一种方法是使用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/
我的版本接受的答案作为一个函数使用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
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
(我对评论特权感到羞耻)
如果您决定放弃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)我有这样的计划表,并希望填充空的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)因此,为了获得"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
希望我能提供帮助.
干杯
京公网安备 11010802040832号 | 京ICP备19059560号-6 