Recently I wanted to find the time between 2 datetimes, but I wanted to exclude weekends. To further complicate matters, I wanted to know hours, not just days.
The “standard” way to do stuff like this is to build out a calendar table that has every date for the next 50 years, along with info like “IsWeekend” or “IsHoliday”.
But I found some snippets around that I hoped to use to my advantage, and what I came up with works very well:
DECLARE @start DATETIME DECLARE @end DATETIME SET @start = '3/7/08 12:50 pm' SET @end = '3/11/08 1:50 am' SELECT (DateDiff(hh, @start, @end) - DateDiff(ww, @start, @end)*2*24)/24.0
I won’t spend a lot of time explaining why it works, but it basically counts the number of weekend “jumps” that are crossed between the 2 dates, and subtracts accordingly.