SQL: The outcome of how DateDiffNoWeekends should work

In a recent post, I discussed questions that had come up about how we should calculate the number of days between two given dates, but excluding the weekends i.e. people just wanted the number of weekdays.

This relates to one of our SDU Tools called DateDiffNoWeekends. Some customers using it felt that it didn’t calculate the days in the way they thought it should. And on reflection, I agreed.

Much of the discussion centred around how we should treat start and end dates, depending upon whether they were on the weekends or not.

I spent a lot of time considering this, and in the end, I think it came down to a pretty simple set of rules. When you specify just a date, you are really specifying the time 00:00:00 (i.e. midnight) on that date. So when I look at the period from StartDate to EndDate, I’m really looking at the whole of the StartDate, and none of the EndDate.

We then modified the logic to apply those rules. So if the period is from a Friday to the next day (Saturday), the number of weekdays would be 1. The period from the Friday to the Sunday (2 elapsed days later) would also be 1, as would the period from the Friday to the Monday (3 elapsed days later).  The only weekday would be the day time of the Friday.

This all seems to then make sense, and the update will be in v14 of SDU Tools that will ship about mid-March.

2019-02-19