Time between two dates excluding night and weekends?

Question

How do I calculate the time between two dates, excluding times during evening/night (out of business hours) from 6 pm - 8 am and weekends in MS SQL?

Example:

Column 1: Time1: 2019-11-28 16:30:00

Column 2: Time2: 2019-11-29 09:00:00

Calculated Difference: 1.5 h + 1 h = 2.5 h

Possible duplicate of [TSQL datediff only business hours](https://stackoverflow.com/questions/27945586/tsql-datediff-only-business-hours) or [How to calculate business hours/minutes between two dates?](https://stackoverflow.com/questions/16522577/datediff-in-business-working-hours-days-only)? — Thom A, Nov 28 '19 at 16:09
Do you also need to exclude other non-business days, such as holidays? A calendar table is your friend. — Dan Guzman, Nov 28 '19 at 16:10
Possible duplicate of [DATEDIFF in Business Working Hours & Days Only](https://stackoverflow.com/questions/16522577/datediff-in-business-working-hours-days-only) — Amira Bedhiafi, Nov 28 '19 at 16:13

score 0 · Answer 1 · answered Nov 28 '19 at 16:33

Try this:

declare @Column1 as smalldatetime, @Column2 as smalldatetime, @Column as smalldatetime
set @Column1 = '2019-11-28 16:30:00'
set @Column2 = '2019-11-29 09:00:00'

set @Column = @Column2

select 
case 
when DATEPART(DW, @Column) in (1, 7) then 0
when  @Column <= dateadd(hour,18,cast(cast(@Column as date) as smalldatetime)) and @Column > dateadd(hour,9,cast(cast(@Column as date) as smalldatetime))
then datediff(minute, @Column, dateadd(hour,18,cast(cast(@Column as date) as smalldatetime)))

when @Column >= dateadd(hour,8,cast(cast(@Column as date) as smalldatetime)) and @Column < dateadd(hour,18,cast(cast(@Column as date) as smalldatetime))
then datediff(minute, dateadd(hour,8,cast(cast(@Column as date) as smalldatetime)), @Column)

end

John Cappelletti · Answer 2 · 2019-11-28T16:42:17.887

Here is an option which uses an ad-hoc tally table

The CROSS APPLY will return the business seconds, then it becomes a small matter to to format. Note: You will have to make allowances for items over 24 hours.

Example

Declare @YourTable table (ID int,DT1 datetime,DT2 datetime)
Insert Into @YourTable values
(1,'2019-11-28 16:30:00','2019-11-29 09:00:00')

Select A.*
      ,Elapsed=format(dateadd(SECOND,Seconds,0),'HH:mm')
 From  @YourTable A
 Cross Apply (
                Select Seconds=sum(1)
                 From (Select Top (DateDiff(SECOND,DT1,DT2)+1) D=DateAdd(SECOND,-1+Row_Number() Over (Order By (Select Null)),DT1) From  master..spt_values n1,master..spt_values n2) A
                 Where DateName(WEEKDAY,D) not in ('Saturday','Sunday')
                  and  convert(time,D) > '08:00' 
                  and  convert(time,D) < '18:00'  
             ) B

Returns

ID  DT1                        DT2                      Elapsed
1   2019-11-28 16:30:00.000    2019-11-29 09:00:00.000  02:30

Time between two dates excluding night and weekends?

2 Answers2