SQL lookup time overlap runs at midnight (after 2 days)

Question

I know that there are many such questions, but I did not see that which was quite similar to my criteria. Therefore, I would like to ask you for help. The fields that I have are just the beginning and the end, which are related to the types of time. I can’t include any specific dates in it. If time ranges do not go through midnight every other day, I would simply compare the two tuples as such:

end1 > start2 AND start1 < end2

(touch endpoints are not considered overlapping here.)

But when I use a time range that passes (or at) midnight, this obviously does not work. For example, given:

 start  |   end
--------+--------
06:00PM | 01:00AM
03:00PM | 09:00PM

Without considering dates, how can I achieve this, please. My assumption is that if the end is less than the initial, then we include 2 days.

I am trying to do this in plain standard SQL, so there is simple and concise logic in the WHERE clause.

Thanks everyone!

---

Added:

Also, how would I check if one time range completely converts another? Thanks again!

Answer 1

If your SQL supports time difference:

(end1 - start1) > (start2 - start1) AND (end2 - start2) > (start1 - start2)

Answer 2

, "" SQL . , . , SQL Server 2008 DateTime, , Time.

Select ...
From    (
        Select Cast(T.Start1 As DateTime) As Start1
            , Case
                When Cast(T.Start1 As DateTime) > Cast(T.End1 As DateTime) Then DateAdd(d,1,Cast(T.End1 As DateTime))
                Else Cast(T.End1 As DateTime)
                End As End1
        From ...
        ) As T
Where T.End1 > T2.Start2 And T1.Start2 < T2.End2            

Answer 3

( )

Answer 4

Transtar. , . 4 , , . , , 5AM, 1:00 12:59. FORTRAN, .