Calculating Business Days and Excluding Holidays in SQL Server
Updated: Jun 4, 2020
I was recently asked how to determine the number of working days between two dates in SQL Server. And for some added complexity, the request was to include the start date as one of the days only if the time is prior to 3:00 PM. Rather than jumping straight to the final answer, I started at the basics of working with dates in SQL and built on those to get to the end result. It’s a fairly common question so it seems like a pretty good blog subject. Without further ado, I give you:
Working with dates in SQL
DateTime objects in SQL can be broken down into individual parts using the 'DATEPART' function. The 'DATEPART' function takes 2 parameters: the interval and the date. The interval is the part of the date that you want to return, such as “year”, “month”, “day”, “hour”, etc. And the date is the value being evaluated:
select DATEPART(MONTH, '1/20/2018') -> returns "1" select DATEPART(DAY, '3/21/2018') -> returns "21" select DATEPART(YEAR, '4/23/2018') -> returns "2018"
Another valid interval part is called "WEEKDAY" which returns some number between 1 and 7. 1 being Sunday and 7 being Saturday. With that we can create a simple check to see if any given day is a weekend day. That would look like this:
select CASE WHEN DATEPART(WEEKDAY, '6/25/2018') IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END
This is good to know and comes in handy quite often, but it doesn't get us to where we want to be yet.
There's also a function called 'DATEDIFF' that will calculate the difference between two dates and return the value in whatever units of measure we specify. This function takes the same parameters as the 'DATEPART' function we discussed earlier. With that we can calculate the difference between two dates in minutes, hours, days, weeks, etc:
select DATEDIFF(DAY, '1/1/2018', '2/1/2018') -> returns "31" select DATEDIFF(WEEK, '1/1/2018', '2/1/2018') -> returns "4"