-1

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.

zaq
  • 135
  • 5
  • 2
    Please provide sample data and desired results. – Gordon Linoff Dec 27 '20 at 19:09
  • 1
    Please read the [Tag Guidance for asking question about SQL](https://stackoverflow.com/tags/sql/info), and modify your question accordingly. – Robert Harvey Dec 27 '20 at 19:11
  • 2
    Does this answer your question? [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) – Danieboy Dec 27 '20 at 19:35
  • @Danieboy I saw that thread, but that method only provides a check for whether or not given ranges overlap. I needed the amount of overlap, too. – zaq Dec 27 '20 at 20:57

1 Answers1

1

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786