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"
Now we know the total number of days between two dates and we know the total number of weeks between those same two dates. We can assume two weekend days per week, so the number of work days would be the total number of days minus 2 days per week. Putting that into an SQL query would look something like this:
select DATEDIFF(DAY, '1/1/2018', '2/3/2018') - DATEDIFF(WEEK, '1/1/2018', '2/3/2018') * 2 -> returns "25" We are getting closer to the final answer, but what we have so far isn't completely accurate because our end date (2/3/2018) is on a weekend. To account for that we'll want to subtract one each if the start or end date falls on a weekend day. The SQL for that looks like this:
select DATEDIFF(DAY, '1/1/2018', '2/3/2018')
- DATEDIFF(WEEK, '1/1/2018', '2/3/2018') * 2
- CASE
WHEN DATEPART(WEEKDAY, '1/1/2018') in (1, 7) THEN 1 ELSE 0
END
- CASE
WHEN DATEPART(WEEKDAY, '2/3/2018') in (1, 7) THEN 1 ELSE 0
ENDAnd now we have 24, which is the number of weekdays between 1/1/2018 and 2/3/2018, not counting the start date. The only remaining step would be to add 1 if the start date is before 3:00 PM. The examples I have used so far have been dates for simplicity, but they could just as easily be DateTime values. The key to this step is the last “CASE” statement:
select
DATEDIFF(DAY, '1/1/2018 08:00', '2/3/2018 08:00')
- DATEDIFF(WEEK, '1/1/2018 08:00', '2/3/2018 08:00') * 2
- CASE
WHEN DATEPART(WEEKDAY, '1/1/2018') in (1, 7) THEN 1 ELSE 0
END
- CASE
WHEN DATEPART(WEEKDAY, '2/3/2018') in (1, 7) THEN 1 ELSE 0
END
+ CASE
WHEN DATEPART(HOUR, '1/1/2018 08:00') < 15 THEN 1
ELSE 0
ENDAnd now we have 25 because the "HOUR" part of the start date is before 3:00 PM (15).
Now, all of this is good information and I've used these methods several times over the years. This is usually sufficient for most workday calculation needs. But keep in mind that "1/1/2018" was a holiday, so our calculation is off by one day if you need to take holidays into account. There is no built-in function in SQL Server that will tell you if a given date is a holiday. To do that requires a table of holidays. This is not all that uncommon; many companies will have a table that contains one record per day with a bit value that indicates if the day is a workday or not.
For this example, we will assume that we have a table named "WORKCALENDAR" which contains one record per day and each record includes a date and a field named “WORKTIME”. The value in that field will be 0 for work days and 1 for non-workdays (weekends and holidays). And with that, we don’t even need to use the DATEPART or DATEDIFF functions to calculate the number of workdays between two dates. We can get to the same result simply by counting the number of records between two dates where "WORKTIME = 0":
select count(*)
from WORKCALENDAR
where DATEVALUE >= '1/1/2018'
and DATEVALUE <= '2/3/2018'
and WORKTIME = 0We’re almost done! Remember that we still need to add 1 if the "HOUR" part of the start date is less than 15. So the final query becomes:
select count(*)
+ CASE
WHEN DATEPART(HOUR, '1/1/2018 08:00') < 15 THEN 1
ELSE 0
END
from WORKCALENDAR
where DATEVALUE >= '1/1/2018 08:00'
and DATEVALUE <= '2/3/2018 08:00'
and WORKTIME = 0Usually the value we are calculating is only one part of a larger query that may include many other fields. In those cases, the example queries would simply be added to the bigger query as a nested query (also known as a subquery). This final example shows it all together in a query that returns the order numbers and the number of work days that have elapsed since the order date and today:
select ORDERNUMBER, po.ORDERDATETIME,
(
select COUNT(*)
+ CASE
WHEN DATEPART(HOUR, po.ORDERDATETIME) < 15 THEN 1 ELSE 0
END
FROM WORKCALENDAR
WHERE DATEVALUE >= po.ORDERDATETIME
and DATEVALUE <= GETDATE()
and WORKTIMECONTROL = 0
) as DaysElapsed
from PURCHORDERS po
where po.ACCOUNT = 'ACCT555'
And that’s it! We have covered a query that calculates the number of work days between two dates using two different methods, and one of those methods can even account for custom holiday schedules.
If you find any this helpful, or if I’ve left anything out, please drop me a line and let me know!
David Koons
Elegant Software Solutions