Eduardo Eduardo - 1 year ago 69
SQL Question

SQL where two time values between two time columns

With MySQL, I need to find rows where two values are between two columns.

For example, I'm creating a schedule from '15:00' to '18:00', but the query must verify if any row has an period in use. If there is a record from '14:00' to '18:30', I need to return it to validate.

I tried something like, but not works:

select * from availabilities where (('15:00' or '18:00') between start_hour and end_hour)

Any idea?

Answer Source

This is basically a "test if range overlaps another range" question. The following query should work as long as start time is less than end time:

FROM availabilities
WHERE '18:00' > start_hour AND end_hour > '15:00'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download