April 21, 2011 at 7:49 pm
Anyone have a ready-made function that will add [x] number of days to a date and then adjust it to the nearest business day (Mon-Fri)?
The code below is a VB.NET version of what I'm looking for. It allows for [n] days to be added (or subtracted if a negative number) to a date and then optionally adjusted if the result falls on a Saturday or Sunday.
Its late, I'm tired and yes, I'll probably play around myself. But thought I'd ask anyway.
Public Shared Function getCalculatedDate(ByVal CalculationBaseDate As Date, ByVal Interval As Integer, Optional ByVal AdjustToNearestBusinessDate As Boolean = False) As Date
Dim NewDate As Date
NewDate = DateAdd(DateInterval.Day, Interval, CalculationBaseDate)
If AdjustToNearestBusinessDate Then
If Interval < 0 Then
If DatePart(DateInterval.Weekday, NewDate) = 1 Then
NewDate = DateAdd(DateInterval.Day, -2, NewDate)
End If
If DatePart(DateInterval.Weekday, NewDate) = 7 Then
NewDate = DateAdd(DateInterval.Day, -1, NewDate)
End If
End If
If Interval > 0 Then
If DatePart(DateInterval.Weekday, NewDate) = 1 Then
NewDate = DateAdd(DateInterval.Day, 1, NewDate)
End If
If DatePart(DateInterval.Weekday, NewDate) = 7 Then
NewDate = DateAdd(DateInterval.Day, 2, NewDate)
End If
End If
End If
getCalculatedDate = NewDate
End Function
April 21, 2011 at 7:55 pm
April 21, 2011 at 9:05 pm
Now none of these are mine but I failed to record who informed me of them.
select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)
AS 'First Monday'
--add 4 days of it to get the first friday
select DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))
AS 'First Friday'
--add 2 weeks to it to get the third friday
select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )
AS '3rd Friday'
An good source of date manipulation is:
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Hopefully the above will get you started ... Next search SSC for the use of a Calendar Table which might prove to be more useful for you.
April 25, 2011 at 3:36 pm
Thanks for that link. I realized over the weekend, that I was asking the wrong question. I don't need to adjust to the nearest week day, but rather need to add actual Business Days to a date which means that the calculation must factor in situations where a weekend is crossed. For example, adding 3 business days to Friday, April 22nd should result in Wed April, 27th.
April 25, 2011 at 4:03 pm
david.holley (4/25/2011)
Thanks for that link. I realized over the weekend, that I was asking the wrong question. I don't need to adjust to the nearest week day, but rather need to add actual Business Days to a date which means that the calculation must factor in situations where a weekend is crossed. For example, adding 3 business days to Friday, April 22nd should result in Wed April, 27th.
In light of the above may I suggest you read this article, seems to be what you need to do
http://www.sqlservercentral.com/articles/Test-Driven+Development/71075/
April 25, 2011 at 4:39 pm
Well, ended up playing around in VBA to come up with this. Actually a bit easier than I thought. I post the T-SQL equivalent shortly. The idea is to increment the date by 1 and test if the new date falls on a Saturday or Sunday and then adjust the date at that point. I need the ability to subtract business days hence the numOfDays > 0 and numOfDays <0.
Public Function ft(numOfDays As Integer)
Dim t As Date
t = Date
If numOfDays > 0 Then
For i = 1 To numOfDays
t = DateAdd("d", 1, t)
If DatePart("w", t) = 1 Then t = DateAdd("d", 1, t)
If DatePart("w", t) = 7 Then t = DateAdd("d", 2, t)
Next i
End If
If numOfDays < 0 Then
For i = 1 To (numOfDays * -1)
t = DateAdd("d", -1, t)
If DatePart("w", t) = 1 Then t = DateAdd("d", -2, t)
If DatePart("w", t) = 7 Then t = DateAdd("d", -1, t)
Next i
End If
ft = t
End Function
April 25, 2011 at 5:26 pm
Review this article: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
I would also recommend that you review the discussion that follows that article.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 25, 2011 at 6:45 pm
Here's what I came up with. It looks like it's working.
CREATE FUNCTION [dbo].[AddBusinessDays]
(
@numOfDays As Integer, @DateBasis As Date
)
RETURNS date
AS
BEGIN
DECLARE @d as date
DECLARE @i as integer
SET @d = @DateBasis
IF @numOfDays > 0
BEGIN
SET @i = 1
WHILE @i <= @numOfDays
BEGIN
SET @i = @i + 1
SET @d = DATEADD("d",1,@d)
IF DATEPART("w",@d) = 1
BEGIN
SET @d = DATEADD("d",1,@d)
END
IF DATEPART("w",@d) = 7
BEGIN
SET @d = DATEADD("d",2,@d)
END
END
END
IF @numOfDays < 0
BEGIN
SET @i = 1
WHILE @i <= @numOfDays * - 1
BEGIN
SET @i = @i + 1
SET @d = DATEADD("d",-1,@d)
IF DATEPART("w",@d) = 1
BEGIN
SET @d = DATEADD("d",-2,@d)
END
IF DATEPART("w",@d) = 7
BEGIN
SET @d = DATEADD("d",-1,@d)
END
END
END
RETURN @d
END
GO
Subtracting Dates...
SELECT dbo.AddBusinessDays(-1,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-1,'4/25/11'))
SELECT dbo.AddBusinessDays(-2,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-2,'4/25/11'))
SELECT dbo.AddBusinessDays(-3,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-3,'4/25/11'))
SELECT dbo.AddBusinessDays(-4,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-4,'4/25/11'))
SELECT dbo.AddBusinessDays(-5,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-5,'4/25/11'))
SELECT dbo.AddBusinessDays(-6,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-6,'4/25/11'))
SELECT dbo.AddBusinessDays(-7,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-7,'4/25/11'))
SELECT dbo.AddBusinessDays(-8,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-8,'4/25/11'))
SELECT dbo.AddBusinessDays(-9,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-9,'4/25/11'))
SELECT dbo.AddBusinessDays(-10,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-10,'4/25/11'))
SELECT dbo.AddBusinessDays(-11,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-11,'4/25/11'))
SELECT dbo.AddBusinessDays(-12,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-12,'4/25/11'))
SELECT dbo.AddBusinessDays(-13,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(-13,'4/25/11'))
Adding Dates...
SELECT dbo.AddBusinessDays(1,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(1,'4/25/11'))
SELECT dbo.AddBusinessDays(2,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(2,'4/25/11'))
SELECT dbo.AddBusinessDays(3,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(3,'4/25/11'))
SELECT dbo.AddBusinessDays(4,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(4,'4/25/11'))
SELECT dbo.AddBusinessDays(5,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(5,'4/25/11'))
SELECT dbo.AddBusinessDays(6,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(6,'4/25/11'))
SELECT dbo.AddBusinessDays(7,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(7,'4/25/11'))
SELECT dbo.AddBusinessDays(8,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(8,'4/25/11'))
SELECT dbo.AddBusinessDays(9,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(9,'4/25/11'))
SELECT dbo.AddBusinessDays(10,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(10,'4/25/11'))
SELECT dbo.AddBusinessDays(11,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(11,'4/25/11'))
SELECT dbo.AddBusinessDays(12,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(12,'4/25/11'))
SELECT dbo.AddBusinessDays(13,'4/25/11'),DATEPART("w",dbo.AddBusinessDays(13,'4/25/11'))
April 25, 2011 at 10:07 pm
I know the guy that wrote that article! 😛
That particular article calculates the number of week days between two given dates and I don't recommend it for this particular problem.
However, someone with the handle of "Fraggle" came up with a good one in the discussion for that article. Here's the link to the post and some comments by me...
http://www.sqlservercentral.com/Forums/FindPost628349.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2011 at 10:50 am
It's implied in some of the referred to postings, but bear in mind that any such effort will almost always then end up needing to take company holidays or other issues into consideration. By far the easiest approach in production is to have a calendar, with one row per day, pointing to whatever you need -- next business day, last business day, fiscal month, fiscal year, closing time -- whatever you need, put it in there, then anyone who needs it can get it with a fast join.
Now getting it in there -- these are all good tools. but I would recommend NOT putting these into the funciton, but rather using them to build a table where you can handle exceptions permanently, rather than (otherwise) starting to build the exceptions into the function.
April 30, 2011 at 12:37 pm
The issue with company and public holidays is certainly there. The current push is to get the basics out. We'll probably end up with a table for the holidays that the procedure validates against. If the initial date from the calculate is found in the table then the date will be adjusted accordingly.
April 24, 2013 at 1:52 am
david.holley (4/30/2011)
The issue with company and public holidays is certainly there. The current push is to get the basics out. We'll probably end up with a table for the holidays that the procedure validates against. If the initial date from the calculate is found in the table then the date will be adjusted accordingly.
Hi David
I've been messing with a similar function for quite a while now - it creates a calendar table on the fly and includes certain holidays as well as outputting the day name so weekends can be eliminated. It's insanely fast at returning a set - I can't get a duration for 10,000 rows running locally. It differ's from Fraggle's solution very slightly - if you add three business days to saturday, my function will return wednesday, Fraggle's returns thursday. Your choice. Here's the function:
ALTER FUNCTION [dbo].[IF_Calendar]
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max
iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)
-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName]= DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence 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] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
WHERE b.FirstWeekDay = @FirstWeekDay
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 24, 2013 at 5:28 am
I really like how easy CROSS APPLY made it to calculate some of the more complex holidays. Well done, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2013 at 5:38 am
Woah! My brain just exploded (a la Mars Attacks!). That'll take some time to digest. It didn't occur to me until just now that most major US holidays observed by private non-banking businesses fall on a specific date, with the exception of Thanksgiving which can be defined as the Thursday where the remaining days in the month is less than 6.
(And just why hasn't MS modified DateDiff and DateAdd across all languages to allow for caluclations such as number of business days between two dates? Since it does come up quite frequently.)
April 24, 2013 at 5:55 am
Jeff Moden (4/24/2013)
I really like how easy CROSS APPLY made it to calculate some of the more complex holidays. Well done, Chris.
Thanks Jeff!
One of the points I meant to make in a recent article (not sure if you've read it :blush:) is exactly that - CROSS APPLY without a table reference (FROM or VALUES) means calculate!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply