Is there a way to find out the overlap of two time ranges in SQL Server? [closed]

I have four datetime2 columns, each representing either a start or an end to two different time ranges. Is it possible to find the overlap of these two in SQL Server? I’ve been having a lot of trouble with it since SQL doesn’t seem to have many built-in functions to handle all the different cases. Thanks for the help.

Answer

This is a bit of a pain in SQL Server, but the overlap is the least end time minus the greatest start time:

select datediff(second,
                (case when start1 > start2 then start1 else start2 end),
                (case when end1 < end2 then end1 else end2 end)
               )

This can return negative values, so you might want:

select (case when start2 < end1 and end2 > start1
             then datediff(second,
                           (case when start1 > start2 then start1 else start2 end),
                           (case when end1 < end2 then end1 else end2 end)
                          )
             else 0
        end)