Question: How should DateDiffNoWeekends work?

One of the tools we have in our free SDU Tools for Developers and DBAs is a version of DATEDIFF that excludes weekends. It’s DateDiffNoWeekends.

This has been a very popular function.

One of the customers who is using these tools found that it was returning different results to what he expected. And it got me re-thinking how it should work.

Let’s start by assuming that weekends are Saturday and Sunday. That’s what most people seem to assume.

Now if I calculate the DATEDIFF (in days) from Monday to Wednesday in a given week, the built-in DATEDIFF function returns 2. So it’s not inclusive of the end-points, only of the distance between the dates.

I can calculate the number of days from today to tomorrow.

SELECT DATEDIFF(day, SYSDATETIME(), DATEADD(day, 1, SYSDATETIME()));

But if today is Sunday, and I ask how many days it is to Monday, and assume that I’m excluding weekends, how many days should that be? 0? 1?

Perhaps because a date is really starting at midnight early on the day, we should treat the first day as part of the period, but the last day as excluded. That would make Saturday to Monday be zero, and same for Sunday to Monday.

However, Friday to Saturday would be 1, as would Friday to Sunday. (because the day time of Saturday and Sunday would not be part of either calculation).

I’m thinking of updating the function based on this feedback, but would love to hear how you would expect such a function to work. Please leave a comment or email me.

2019-02-12