CODEWITHSUNDEEP
sql-server
Nam Nguyen
Nam Nguyen

Reputation: 13

How do I check time range in time range and get redundant time in SQL?

On the assumption that I have 4 variables:

@start = 5 AM
@end = 8 AM
@from = 10 PM
@to = 6 AM

@from and @to are called working time. @start and @end are called morning time.

How do I use SQL to check "working time" in the "morning time"? If it happens, I need to get the redundant time.

I mean from 5AM to 6AM equals 1 hour(s). The following image explains better what I mean: enter image description here

Upvotes: 1

Views: 67

Answers (1)

shadow
shadow

Reputation: 1903

the pseudo-code is:

if ( ( @start < @to ) and ( @end > @from ) )
  return @to - @start

Of course you understand that variables must be of DateTime type as the two @from and @to span over two days.

Edited with full example

declare @from datetime = '2015-12-31 22:00'
declare @to datetime = '2016-01-01 06:00'

declare @start datetime = '2016-01-01 05:00'
declare @end datetime = '2016-01-01 08:00'

if ( ( @start < @to ) and ( @end > @from ) )
  select datediff(hour, @start, @to)

Result = 1

Upvotes: 1

Related Questions