Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

  • 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

  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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.

  • 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/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

  • 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

  • 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'))

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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.

  • 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

    )

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • I really like how easy CROSS APPLY made it to calculate some of the more complex holidays. Well done, Chris.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.)

  • 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!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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