Как рассчитать количество рабочих дней между двумя датами в SQL Server?
С понедельника по пятницу, и он должен быть T-SQL.
Количество рабочих дней между двумя датами
Ответ 1
В рабочие дни с понедельника по пятницу вы можете сделать это с помощью одного 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)
Если вы хотите включить праздничные дни, вам нужно немного поработать...
Ответ 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 [email protected] 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
Если вам нужно использовать собственный календарь, вам может потребоваться добавить некоторые проверки и некоторые параметры. Надеюсь, это послужит хорошей отправной точкой.
Ответ 3
Все кредиты Богдану Максиму и Питеру Мортенсену. Это их сообщение, я просто добавил праздники к функции (Предполагается, что у вас есть таблица "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 [email protected] 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)
*/
Ответ 4
Другим подходом к расчету рабочих дней является использование цикла WHILE, который в основном выполняет итерацию через диапазон дат и увеличивает его на 1, если дни будут найдены в течение понедельника по пятницу. Полный script для расчета рабочих дней с использованием цикла 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/
Ответ 5
Моя версия принятого ответа как функции с помощью 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
Ответ 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 @[email protected]@ReducedDayswithEndDate
SET @[email protected]/7;
SET @[email protected]%7;
SET @WorkDays=(@WeekPart*5)[email protected]
RETURN @WorkDays
Ответ 7
(Я немного отстаю от комментариев)
Если вы решите отказаться от +1 дня в элегантном решении CMS, обратите внимание, что если ваша дата начала и дата окончания находятся в одни и те же выходные, вы получаете отрицательный ответ. Т.е., 2008/10/26 по 2008/10/26 возвращается -1.
мое довольно упрощенное решение:
select @Result = (..CMS 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
Надеюсь, что я мог бы помочь.
Приветствия
Ответ 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
Ответ 10
Использование таблицы дат:
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.
Ответ 11
Это в основном ответ 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 году и опубликовал статью Ицик Бен-Ган. (https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx) Хотя требуется небольшая настройка, поскольку более новые типы date не допускают арифметику дат, в остальном она идентична.
РЕДАКТИРОВАТЬ: я добавил обратно +1 который был каким-то образом +1. Стоит также отметить, что этот метод всегда считает дни начала и окончания. Предполагается также, что дата окончания находится на или после даты начала.
Ответ 12
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 @[email protected]@ReducedDayswithEndDate
SET @[email protected]/7;
SET @[email protected]%7;
SET @WorkDays=(@WeekPart*5)[email protected]
SELECT @WorkDays
Ответ 13
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
Ответ 14
Здесь я привел различные примеры, но в моей конкретной ситуации у нас есть @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
Ответ 15
Если вам нужно добавить рабочие дни к указанной дате, вы можете создать функцию, которая зависит от таблицы календаря, описанной ниже:
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
Ответ 16
Я знаю, что это старый вопрос, но мне нужна формула для рабочих дней, исключая дату начала, так как у меня есть несколько пунктов, и мне нужно, чтобы дни накапливались правильно.
Ни один из не повторяющихся ответов не сработал для меня.
Я использовал определение как
Количество переходов с полуночи на понедельник, вторник, среду, четверг и пятницу
(другие могут считать от полуночи до субботы вместо понедельника)
Я закончил с этой формулой
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 */
Ответ 17
Это работает для меня, в моей стране в субботу и воскресенье - нерабочие дни.
Для меня важно время @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
Ответ 18
Создать функцию, например:
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 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
Ответ 19
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
End
Ответ 20
Я нашел ниже TSQL довольно элегантное решение (у меня нет полномочий для запуска функций). Я обнаружил, что DATEDIFF игнорирует DATEFIRST, и я хотел, чтобы мой первый день недели был понедельником. Я также хотел, чтобы первый рабочий день был установлен на ноль, и если он выпадает на выходные в понедельник, будет ноль. Это может помочь кому-то, у кого есть несколько другое требование:)
Он не обрабатывает праздничные дни
SET DATEFIRST 1
SELECT
,(DATEDIFF(DD, [StartDate], [EndDate]))
-(DATEDIFF(wk, [StartDate], [EndDate]))
-(DATEDIFF(wk, DATEADD(dd,[email protected]@DATEFIRST,[StartDate]), DATEADD(dd,[email protected]@DATEFIRST,[EndDate]))) AS [WorkingDays]
FROM /*Your Table*/
Ответ 21
Один из подходов заключается в "обходе дат" от начала до конца в сочетании с выражением падежа, которое проверяет, не является ли день субботой или воскресеньем, и помечает его (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)
Ответ 22
Как и в случае с 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
С наилучшими пожеланиями!
Ответ 23
Я позаимствовал некоторые идеи у других, чтобы создать свое решение. Я использую встроенный код, чтобы игнорировать выходные и федеральные праздники США. В моей среде EndDate может быть нулевым, но он никогда не будет предшествовать 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 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 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