How to accurately check date range to prevent overlapping:mysql

July 29, 2018

Q:I have a table with events , and each event has an auditorium id,start_date and end_date.In need to insert a new event in to the table without any overlapping on date range in an auditorium

 

select IF(count(*)>0, true, false) from events where ‘YOUR_START_DATE‘ <= end_date and ‘YOUR_END_DATE‘ >= start_date;

OR

select IF(count(*)>0, true, false) from events where ‘YOUR_START_DATE‘  BETWEEN start_date AND end_date or ‘YOUR_END_DATE‘ BETWEEN start_date and end_date and end_date or (‘YOUR_START_DATE‘ < start_date and ‘YOUR_END_DATE‘ > end_date);

If true do not proceed with insert if false continue

 

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *