Add business days to date in SQL without loops
This answer has been significantly altered since it was accepted, since the original was wrong. I'm more confident in the new query though, and it doesn't depend on DATEFIRST
I think this should cover it:
declare @fromDate datetime
declare @daysToAdd int
select @fromDate = '20130123',@DaysToAdd = 4
declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')
;with Numbers as (
select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
from
Split t
left join
Numbers n
on
t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck
We split the time to be added into a number of weeks and a number of days within a week. We then use a small numbers table to work out if adding those few days will result in us hitting a Saturday. If it does, then we need to add 2 more days onto the total.
SQL Server function to add working days to a date
create table holidays (
date date);
GO
create function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
select thedate
from (
select thedate=dateadd(d,v.day,cast(@date as date)),
rn=row_number() over (order by v.day)
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
where h.date is null and left(datename(dw,dateadd(d,v.day,cast(@date as date))),1) <> 'S'
) x
where @days = rn
)
end
GO
Unless you have long holidays, 10 days should be enough to find the 5th next working day. Increase it if you need to.
If you need a larger number of business days from a date, you can use this which will cater for a year or three.
alter function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
select thedate
from (
select thedate=dateadd(d,v.number,cast(@date as date)),
rn=row_number() over (order by v.number)
from master..spt_values v
left join holidays h on h.date = dateadd(d,v.number,cast(@date as date))
where h.date is null and left(datename(dw,dateadd(d,v.number,cast(@date as date))),1) <> 'S'
and v.number >= 1 and v.type='p'
) x
where @days = rn
)
end
GO
SQL - Subtract Business Days from Date
Since you only care about weekends and not other holidays, it is easy to calculate the number of calendar days from the business days, using the knowledge that there are 5 business days in every calendar week (7 days). If you are OK with a scalar function you can use the following:
CREATE FUNCTION SubBusinessDays(
@days int,
@date datetime
) RETURNS int
BEGIN
SET @days = @days-1; -- number of days are inclusive of the start date
SET @date = DATEADD(DAY, -(@days / 5 * 7 + @days % 5),
DATEADD(DAY, (CASE (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7
WHEN 0 THEN -1
WHEN 1 THEN -2
ELSE 0 END), @date));
RETURN DATEADD(DAY, (CASE WHEN (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7 IN (0, 1)
THEN -2 ELSE 0 END), @date);
END
If on the other hand you need to work on a table you can use CROSS APPLY to do the same in steps as in the following sample:
DECLARE @t TABLE(StartDate Datetime, BDays int)
INSERT INTO @t
SELECT d, ofs
FROM (VALUES ('20190906'), ('20190907'), ('20190908'), ('20190909'), ('20190910')) AS sd(d)
CROSS JOIN
(VALUES (7), (8), (9), (10), (11)) AS bd(ofs)
SELECT StartDate, BDays, EndDate
FROM @t
CROSS APPLY (SELECT BDaysMinus1 = BDays-1) x1
CROSS APPLY (SELECT EndDateTemp = DATEADD(DAY, -((BDaysMinus1 / 5) * 7 + BDaysMinus1 % 5),
DATEADD(DAY, (CASE (DATEPART(WEEKDAY, StartDate) + @@DATEFIRST) % 7
WHEN 0 THEN -1
WHEN 1 THEN -2
ELSE 0 END), StartDate))) x2
CROSS APPLY (SELECT EndDate = DATEADD(DAY, (CASE WHEN (DATEPART(WEEKDAY, EndDateTemp) + @@DATEFIRST) % 7 IN (0,1)
THEN -2 ELSE 0 END), EndDateTemp)) x3
Function to add date +3 working days excluding weekends and holidays
As others have pointed out, this is far from the best way to solve this issue. However going with getting your code working there are the following issues, corrected in the code that follows:
You needed to check that the holiday date was the date being processed by the loop, otherwise you added it on every time you ran through the loop.
To do that you needed your
@DateHolidayvariable to be of type date not datetime.To ensure that the weekend skip code works you need to apply it before you add a day
Also the output from "2019-05-17" with a holiday on "2019-05-23" is still "2019-05-22" but the output from "2019-05-18" is now "2019-05-24" i.e. its then added another day to account for the holiday day.
ALTER FUNCTION [dbo].[DATEADDEXCLUDEWD]
(
@addDate AS DATE
, @numDays AS INT
)
RETURNS DATETIME
AS
BEGIN
-- Needs to be a date type to allow for a date to date compare in the holiday section
DECLARE @DateHoliday DATE
WHILE @numDays > 0
BEGIN
--For weekend
-- Add these before the regular add date, as otherwise we've already moved the date forward 1 day
IF DATENAME(DW, @addDate) = 'saturday' SET @addDate = DATEADD(d, 1, @addDate)
IF DATENAME(DW, @addDate) = 'sunday' SET @addDate = DATEADD(d, 1, @addDate)
SET @addDate = DATEADD(d, 1, @addDate)
--For Holiday
IF EXISTS(SELECT DISTINCT hol_date FROM [Vacation].[dbo].[Lkp_Holiday] WHERE hol_date > GETDATE())
BEGIN
DECLARE M_CURSOR CURSOR
FOR SELECT DISTINCT hol_date FROM [Vacation].[dbo].[Lkp_Holiday] WHERE hol_date > GETDATE()
OPEN M_CURSOR
FETCH NEXT FROM M_CURSOR INTO @DateHoliday
WHILE @@FETCH_STATUS = 0
BEGIN
-- Only add the day if we've on the holiday day
if @DateHoliday = @addDate begin
SET @addDate = DATEADD(DAY, 1, @addDate)
end
FETCH NEXT FROM M_CURSOR INTO @DateHoliday
END
CLOSE M_CURSOR
DEALLOCATE M_CURSOR
END
SET @numDays = @numDays - 1
END
RETURN CAST(@addDate AS DATETIME)
END
MYSQL Add working days to date
Try this:
SELECT DATE_ADD(
date_field,
INTERVAL 5 +
IF(
(WEEK(date_field) <> WEEK(DATE_ADD(date_field, INTERVAL 5 DAY)))
OR (WEEKDAY(DATE_ADD(date_field, INTERVAL 5 DAY)) IN (5, 6)),
2,
0)
DAY
) AS FinalDate
FROM `table_name`;
How it works:
- Firstly, it will add 5 days on your date.
- Secondly, when
date_fieldand 5 days later are in two different weeks, it must be added additional 2 days. - Thirdly, when 5 days later is
SatorSun, it must be added additional 2 days.
Count each days of week between two dates without loop
The date range is 30 days, dividing by 7 gives quotient 4 and remainder 2.
So every day of the week gets 4 and two days need an additional one. These are the ones corresponding to @start_date and the following day in this case.
SQL to implement this approach is below (demo)
SELECT DATENAME(WEEKDAY,base_date),
quotient + IIF(Nums.N < remainder, 1, 0)
FROM (VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6)) Nums(N)
CROSS APPLY(SELECT 1 + DATEDIFF(DAY,@start_date,@end_date)) DC(day_count)
CROSS APPLY(SELECT DATEADD(DAY, Nums.N, @start_date), day_count/7, day_count% 7) D(base_date, quotient, remainder)
ORDER BY DATEPART(DW,base_date)
Count work days between two dates
For workdays, Monday to Friday, you can do it with a single SELECT, like this:
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...
Calculate business days skipping holidays and weekends
Also don't forget when adding the accumulated extra days (being weekends and holidays), those might cover new weekends and holidays, so you have to do this "recursively".
Simplest solution
The simplest solution could start from the initial date, increment it by a day, and check each if it's a skippable (weekend or holiday) day or not. If not, decrement the number of days, and repeat until you added as many as needed.
This is how it could look like:
func addDays(start time.Time, days int) (end time.Time) {
for end = start; days > 0; {
end = end.AddDate(0, 0, 1)
if !skippable(end) {
days--
}
}
return end
}
func skippable(day time.Time) bool {
if wd := day.Weekday(); wd == time.Saturday || wd == time.Sunday {
return true
}
if isHoliday(day) {
return true
}
return false
}
func isHoliday(day time.Time) bool {
return false // TODO
}
Testing it:
d := time.Date(2022, time.April, 14, 0, 0, 0, 0, time.UTC)
fmt.Println(addDays(d, 0))
fmt.Println(addDays(d, 1))
fmt.Println(addDays(d, 10))
Which outputs (try it on the Go Playground):
2022-04-14 00:00:00 +0000 UTC
2022-04-15 00:00:00 +0000 UTC
2022-04-28 00:00:00 +0000 UTC
Faster solution
A faster solution can avoid the loop to step day by day.
Calculating weekend days: Knowing what day the initial date is, and knowing how many days you want to step, we can calculate the number of weekend days in between. E.g. if we have to step 14 days, that's 2 full weeks, that surely includes exactly 4 weekend days. If we have to step a little more, e.g. 16 days, that also includes 2 full weeks (4 weekend days), and optionally 1 or 2 more days which we can easily check.
Calculating holidays: We may use a trick to list the holidays in a sorted slice (sorted by date), so we can easily / quickly find the number of days between 2 dates. We can binary search in a sorted slice for the start and end date of some period, and the number of holidays in a period is the number of elements between these 2 indices. Note: holidays falling on weekends must not be included in this slice (else they would be accounted twice).
Let's see how this implementation looks like:
// holidays is a sorted list of holidays
var holidays = []time.Time{
time.Date(2022, time.April, 15, 0, 0, 0, 0, time.UTC),
}
func addDaysFast(start time.Time, days int) (end time.Time) {
weekendDays := days / 7 * 2 // Full weeks
// Account for weekends if there's fraction week:
for day, fraction := start.AddDate(0, 0, 1), days%7; fraction > 0; day, fraction = day.AddDate(0, 0, 1), fraction-1 {
if wd := day.Weekday(); wd == time.Saturday || wd == time.Sunday {
weekendDays++
}
}
end = start.AddDate(0, 0, days+weekendDays)
first := sort.Search(len(holidays), func(i int) bool {
return !holidays[i].Before(start)
})
last := sort.Search(len(holidays), func(i int) bool {
return !holidays[i].Before(end)
})
// There are last - first holidays in the range [start..end]
numHolidays := last - first
if last < len(holidays) && holidays[last].Equal(end) {
numHolidays++ // end is exactly a holiday
}
if numHolidays == 0 {
return end // We're done
}
// We have to add numHolidays, using the same "rules" above:
return addDaysFast(end, numHolidays)
}
Testing it:
d := time.Date(2022, time.April, 14, 0, 0, 0, 0, time.UTC)
fmt.Println(addDaysFast(d, 0))
fmt.Println(addDaysFast(d, 1))
fmt.Println(addDaysFast(d, 10))
Output (try it on the Go Playground):
2022-04-14 00:00:00 +0000 UTC
2022-04-18 00:00:00 +0000 UTC
2022-04-29 00:00:00 +0000 UTC
Improving addDaysFast()
There are still ways to improve addDaysFast():
- the initial loop to check for weekend days in the fraction week could be substituted with an arithmetic calculation (see example)
- the recursion could be substituted with an iterative solution
- an alternative solution could list weekend days as holidays, so the first part to calculate weekend days could be eliminated (duplicates must not be included)
Related Topics
Restrict an SQL Server Connection to a Specific Ip Address
SQL Server 2005 Row_Number() Without Order By
Ora 00904 Error:Invalid Identifier
Use '=' or Like to Compare Strings in SQL
Difference of Two Date Time in SQL Server
MySQL Full Text Search for Words with Three or Less Letters
Creating Temporary Database That Works Across Maven Test Phases
Select SQL Server Database Size
Sqlplus Statement from Command Line
SQL - Does the Order of Where Conditions Matter
Linux Python3 - Can't Open Lib 'SQL Server'
Why Postgres Returns Unordered Data in Select Query, After Updation of Row
Generating Random Strings with T-Sql
How to Add a Auto_Increment Primary Key in SQL Server Database