## DEV Community π©βπ»π¨βπ» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Csaba Boros

Posted on

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
• 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
when @dayOfWeek = 6 then -- Saturday
else
end
end
``````

The bellow table lists a few test cases:

Aaron Reese

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.

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 \$125/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: boros.csaba94@gmail.com
``````

## Update Your DEV Experience Level:

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. π