Chris May

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.