星期一至星期五,它必须是T-SQL。
#1 楼
对于星期一至星期五的工作日,您可以使用单个SELECT来完成,例如:位...
评论
我只是意识到这段代码并不总是有效!我试过这个:SET @StartDate = '28 -mar-2011'SET @EndDate = '29 -mar-2011'答案将其计为2天
–greektreat
2011年3月30日14:33
@greektreat工作正常。只是@StartDate和@EndDate都包含在计数中。如果要将星期一至星期二视为1天,只需在第一个DATEDIFF之后删除“ +1”即可。然后您还将获得Fri-> Sat = 0,Fri-> Sun = 0,Fri-> Mon = 1。
–乔·戴利(Joe Daley)
2011年4月4日,1:11
作为@JoeDaley的后续活动。当您在DATEDIFF之后删除+ 1以从计数中排除开始日期时,您还需要调整CASE部分。我最终使用了这个例子:+(CASE WHEN DATENAME(dw,@StartDate)='Saturday'THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw,@EndDate)='Saturday'THEN 1 ELSE 0 END)
– Sequenzia
2012年2月15日在18:45
datename函数是与语言环境相关的。一个更健壮但也更晦涩的解决方案是将最后两行替换为:-(case datepart(dw,@StartDate)+ @@ datefirst,当8然后1否则0结束)-(case datepart(dw,@EndDate)+ @@ datefirst当7然后1时14然后1否则0结束)
–托本·克莱恩(Torben Klein)
2012年8月20日9:09
为了澄清@Sequenzia的评论,您将完全删除有关星期日的案例声明,仅保留+(CASE WHEN DATENAME(dw,@StartDate)='Saturday'THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw,@EndDate) ='Saturday'THEN 1 ELSE 0 END)
–安迪·拉达兹(Andy Raddatz)
16-2-17在21:01
#2 楼
在“计算工作日”中,您可以找到有关此主题的不错的文章,但是您可以看到它并不那么高级。--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
如果需要使用自定义日历,您可能需要添加一些检查和一些参数。希望它将提供一个很好的起点。
评论
感谢您提供链接以了解其工作原理。在sqlservercentral上写的很棒!
–克里斯·波特(Chris Porter)
13年2月26日在22:36
#3 楼
全部归功于Bogdan Maxim和Peter Mortensen。这是他们的帖子,我刚刚在函数中添加了假日(假设您有一个表“ tblHolidays”,其日期时间字段为“ HolDate”。--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)
*/
评论
嗨,丹B。您只想让您知道您的版本假定表tblHolidays不包含星期六和星期一,有时这种情况会发生。无论如何,感谢您分享您的版本。干杯
–朱利奥·诺布雷(Julio Nobre)
13年11月25日在11:42
Julio-是的-我的版本确实假定星期六和星期日(不是星期一)为周末,因此不是“非营业日”。但是,如果您在周末工作,那么我想每天都是“工作日”,您可以注释掉条款中的周六和周日部分,然后将所有假期添加到tblHolidays表中。
– Dan B
2013年12月5日17:20
谢谢丹。我将其合并到我的函数中,添加了一个周末检查,因为我的DateDimensions表包含所有日期,节假日等。以您的函数为例,我刚刚添加了:并且IsWeekend = 0在StartDate和EndDate之间的[HolDate]之后)
– AlsoKnownAsJazz
18-10-11在14:32
如果“假期”表包含周末的假期,则可以修改以下条件:在2和6之间的@StartDate AND @EndDate AND DATEPART(dw,HolDate)之间的HolDate位置仅计算星期一至星期五的假日。
–安德烈
19年3月14日在17:24
#4 楼
另一种计算工作日的方法是使用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循环选项更简洁,使用的代码行更少,但它有潜力可能会成为环境中的性能瓶颈,尤其是当您的日期范围跨越数年时。 /www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
#5 楼
我使用版本DATEPART
接受的答案作为函数,因此我不必在DATENAME(dw, @StartDate) = 'Sunday'
上进行字符串比较。无论如何,这是我的业务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
#6 楼
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
评论
如果您发布代码,XML或数据示例,请在文本编辑器中突出显示这些行,然后单击编辑器工具栏上的“代码示例”按钮({}),以很好地格式化和语法突出显示它!
– marc_s
2011年1月20日上午11:30
很好,不需要外围功能或使用此功能来更新数据库。谢谢。爱萨尔提尔顺便说一句:-)
–布赖恩·斯科特(Brian Scott)
2011-12-15 11:35
超级解决方案。我在变量公式中添加了公式,以便在webi Universe中使用它来计算2个表列中的日期之间的工作日(MF),例如...((((DATEDIFF(day,table.col1,table.col2)+1)- (((CASE DATENAME(工作日,table.col2)当'星期六'然后THE 1当'星期日'然后2 ELSE 0 END)))/ 7)* 5)+(((DATEDIFF(day,table.col1,table.col2 )+1)-((CASE DATENAME(工作日,table.col2)当'星期六'则1时'当'星期日'则2否则0结束)))%7)
–希拉里
19年4月4日在15:05
#7 楼
(我对发表评论的特权有些害羞)如果您决定放弃CMS优雅解决方案中的+1天,请注意,如果您的开始日期和结束日期在同一周末,得到否定的答案。即,从2008/10/26到2008/10/26返回-1。我的解决方法比较简单:
select @Result = (..CMS's answer..)
if (@Result < 0)
select @Result = 0
RETURN @Result
#8 楼
对于包括假期在内的日期之间的差异,我采用这种方式: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
希望能为您提供帮助。
干杯
评论
关于假期减去。如果开始日期是1月1日,结束日期是12月31日怎么办?您将只减去2-这是错误的。我建议使用DATEDIFF(day,Start_Date,Date)和End_Date相同,而不是整个'SELECT COUNT(*)FROM Holiday...。
–伊利亚·拉特凯维奇(Illia Ratkevych)
13年3月22日在16:06
#9 楼
这是一个效果很好的版本(我认为)。假期表包含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
评论
那些假期可能也落在周末。对于某些人来说,星期日的假期将由下一个星期一代替。
–爱侣湾Soetomo
16年2月2日在10:05
#10 楼
我知道这是一个老问题,但是我需要一个工作日的公式,但要排除开始日期,因为我有几个项目,并且需要正确地积累天数。没有一个迭代式的答案都对我无效。
我使用了类似这样的定义:
从午夜到星期一,星期二,星期三,星期四和星期五的次数已通过
(其他人可能会从午夜到星期六而不是星期一)
我得出了这个公式
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 */
评论
那个为我做了,但是我不得不做些零钱。 @StartDate是星期六还是星期五,这并没有说明。这是我的版本:DATEDIFF(day,@StartDate,@EndDate)-DATEDIFF(week,@StartDate,@EndDate)-DATEDIFF(week,DATEADD(day,1,@StartDate),DATEADD(day,1,@EndDate)) -(在DATEPART(WEEKDAY,@StartDate)IN(1,7)THEN 1 ELSE 0 END的情况下)+ 1
–caiosm1005
19年8月7日,9:50
@ caiosm1005,从星期六到星期日返回0,从星期六到星期一返回1,从星期五到星期六返回0。所有这些与我的定义一致。您的代码将无法正确累积(例如,星期五至星期五返回6,星期一至星期一返回5)
– adrianm
19-10-16在6:36
#11 楼
这基本上是CMS的答案,而无需依赖特定的语言设置。而且由于我们正在研究通用名称,这意味着它也应适用于所有@@datefirst
设置。 ,因此表达式是确定性的,不需要修改(只要我们对工作日的定义从星期一到星期五一直是一致的即可。)日期编号的确会根据datediff(week, ...)
设置而有所不同,修改后的计算方法会以较小的复杂度处理此更正一些模块化算术。处理周六/周日事务的一种更干净的方法是在提取星期几值之前转换日期。移位后,这些值将与固定的(并且可能更熟悉)编号重新对齐,该编号从星期日的1开始,到星期六的7结尾。我至少可以追溯到2002年和Itzik Ben-Gan的文章中就找到这种解决方案的形式。 (https://technet.microsoft.com/zh-cn/library/aa175781(v=sql.80).aspx)尽管由于新的
@@datefirst
类型不允许日期算术而需要进行一些细微调整,但在其他方面相同。 编辑:
我添加了已经被遗弃的
date
。还值得注意的是,此方法总是计算开始和结束的天数。它还假定结束日期在开始日期或之后。评论
请注意,这将在周末的许多日期返回错误的结果,因此它们不会添加upp(星期五->星期一应与星期五->星期六+星期六->星期日+星期日->星期一相同)。 Fri-> Sat应该为0(正确),Sat-> Sun应该为0(错误-1),Sun-> Mon应该为1(错误0)。随之而来的其他错误是Sat-> Sat = -1,Sun-> Sun = -1,Sun-> Sat = 4
– adrianm
18年7月4日在12:23
@adrianm我相信我已经纠正了问题。实际的问题是它总是被一个人关闭,因为我意外地掉了那一部分。
–shawnt00
18年7月4日在17:53
感谢更新。我认为您的公式已排除了我需要的开始日期。我自己解决了这个问题,并将其添加为另一个答案。
– adrianm
18年7月5日在13:09
#12 楼
使用日期表: 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创建数字表。
#13 楼
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
评论
如果要使用函数,最好使用基于表的函数,如MárioMeyrelles的回答
–詹姆斯·詹金斯(James Jenkins)
2015年9月2日,12:52
#14 楼
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
#15 楼
我在这里采用了各种示例,但是在我的特殊情况下,我们有一个@PromisedDate来交付,并有一个@ReceivedDate来实际接收该项目。在“ PromisedDate”之前收到项目时,除非我按日历顺序对传递到函数中的日期进行排序,否则计算将无法正确总计。我不想每次都检查日期,因此我更改了为我处理日期的功能。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
#16 楼
如果需要将工作日添加到给定的日期,则可以创建依赖于日历表的函数,如下所述: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
评论
+1我最终在这里使用了类似的解决方案
–詹姆斯·詹金斯(James Jenkins)
2015年9月2日,12:49
#17 楼
与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
最好的问候!
评论
完善!这就是我想要的。特别感谢!
–幻影
4月30日18:56
#18 楼
这对我来说很有效,在周六和周日在我的国家/地区是非工作日。@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
#19 楼
创建类似的函数: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
#20 楼
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
结束
#21 楼
我发现下面的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*/
#22 楼
一种方法是与案例表达式一起从头到尾“遍历日期”,该案例表达式检查日期是否是星期六或星期日,并对其进行标记(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)
#23 楼
我从别人那里借了一些想法来创建我的解决方案。我使用内联代码忽略周末和美国联邦假日。在我的环境中,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
#24 楼
以上功能均不能在同一星期工作或处理假期。我是这样写的: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
评论
我刚刚将此代码添加到代码项目codeproject.com/Tips/5284659/中的一篇文章中。
–伊戈尔·克鲁皮茨基(Igor Krupitsky)
11月7日7:13
评论
您可以定义工作日吗?星期一到星期五吗?不包括重大假期?什么国家?必须在SQL中完成吗?