In SQL how to get the duration within 2 dates subtracting a timeframe whenever it occurs

Aparna Source

I need to calculate the duration between 2 datetimes excluding breaks. I calculate total time using for example if t1 is '02/12/2018 14:18:29' and t2 is '02/14/2018 14:18:29'

select datediff(second,t1,t2)

Now if I want to exclude the time there were breaks like if 14:30 to 14:45 was a break every day, how do I create my query.

I tried

    datediff(minute, t1, t2) -
            when cast(t1 as time) > '14:30:00:000' and cast(t2 as time) < '14:45:00:000'  
               then 15 
               else 0 
         end as breaks)

But I don't seem to get the right answer



comments powered by Disqus