Adding business days to a date using SQL

Recently I had a task that seemed very simple at first. I had a table with billing start dates and number of business days to bill. The task was to calculate the end date for each row. Basically to calculate the start date + number of business days without having to worry about holidays.

I was surprised that there is no built-in solution for this seemingly common problem. Here is my own take on the problem.

The logic is quite simple:

• for each 5 working days add an entire calendar week to the starting date
• after adding the whole weeks add the remaining days (for example adding 8 days means adding 1 week and 3 days)
• if the resulting date is a Saturday then add 2 more days
• if the resulting date is a Saturday then add 1 more day

And here is the resulting code:

create function addWorkingDays(@startDate datetime, @workingDays int)
returns datetime
as begin
    if @workingDays = 0
        return @startDate

    declare @calendarDays int = 
        (@workingDays / 5) * 7 +  -- add whole weeks 
        (@workingDays % 5) -- add remaining days

    declare @resultDate datetime = dateadd(d, @calendarDays, @startDate)

    declare @dayOfWeek int = ((datepart(DW, @resultdate) - 1) + @@datefirst ) % 7 --the @@datefirst part is needed if you are outside of US where 0 is Monday 

    return case 
        when @dayOfWeek = 0 then --Sunday
            dateadd(d, @calendarDays, @startDate) + 1
        when @dayOfWeek = 6 then -- Saturday
            dateadd(d, @calendarDays, @startDate) + 2
        else
            dateadd(d, @calendarDays, @startDate)
    end
end

The bellow table lists a few test cases:

Comments

[Csaba Boros]

Please let me know if you see any issues with my solution or if you have any ideas or suggestions to make it better!

I am available for part-time or full-time contract work, preferably on a long-term basis, but I'm also available for smaller projects.
I am very flexible with my working hours (mostly working with clients from USA). 
I am a full-stack developer with more than 8 years of professional experience mostly in the C# .NET stack as a freelance developer. I don't shy away from the DevOps and DBA parts of the project either. I also like to create beautiful UI's that users love.
I prefer a long-term contract, but I'm also available for smaller projects.
I also work through Toptal. My CV is available on my toptal profile and my website.
My rate is $90/hour. 
Some of the technologies: C# .NET MVC, React,  JavaScript, REST, SQL, Azure DevOps, Circle CI, AWS, Docker, Blazor, WCF, SOAP, SCSS, Kubernetes, Rabbit MQ etc.

Github: https://github.com/boros-csaba
Website: https://www.boroscsaba.com/
Toptal: https://www.toptal.com/resume/boros-csaba
Email: [e-mail]

[Aaron Reese]

What about bank/national holidays?
A better solution to have a calendar table. This holds the date and a number of columns that are relevant to your particular requirements but typically
Day of week, day of month, day of year, week of year, month , quarter, financial month, financial quarter, is weekday, is weekend, is last day of month, is holiday USA, is holiday Canada is holiday Mexico

You can then use this to pick days in a range and just add up IsWeekday - IsHolidayUSA of your date range.

[Csaba Boros]

Yes, you are right. Things can get more complicated. For my use-case I didn't have to worry about holidays, but I am thinking on writing a follow-up tutorial where I also account for holidays with a calendar table.