-3

I'm stuck with a SQL query.

I have a situation like this: in the bookings table. I have two simple DATETIME columns date_start and date_end, and I have two datepickers where the user can select those two dates and values from them we should call dp_date_start and dp_date_end.

Rows in the result set need to fit into following criteria:

  • if date_start and date_end are completely inside that range
  • if only date_start or date_end are in that range
  • if date_start or date_end are border values

Basically it's a booking logic, imagine it that's a room that cannot be booked if it's already occupied.

What I have tried so far:

SELECT * 
FROM bookings 
WHERE 'dp_date_start' BETWEEN start_date AND end_date 
   OR 'dp_date_end' BETWEEN start_date AND end_date 
   OR start_date BETWEEN 'dp_date_start' AND 'dp_date_end' 
   OR end_date BETWEEN 'dp_date_start' AND 'dp_date_end';

But if fails if i.e. 'dp_date_start' is equal to end_date.

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Miljan Puzović
  • 5,840
  • 1
  • 24
  • 30
  • 1
    Tag your question with the database you are using. In addition, the single quotes mean your query is invalid SQL and should generate an error. – Gordon Linoff Jan 19 '21 at 01:53
  • I know, look at it as a pseudo-code, since I'm using this in C#. It doesn't need to be 100% syntax valid, I just need the logic. And btw single quotes are ok in C# in SQL :) – Miljan Puzović Jan 19 '21 at 01:55

2 Answers2

1

You have two start dates and two end dates. You can check if both start dates are between both the period as follows:

SELECT * FROM bookings 
WHERE dp_date_start between start_date AND end_date 
   OR start_date BETWEEN dp_date_start and dp_date_end
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Thanks, this looks promising. I'm getting proper results in PhpMyAdmin in improvised model of that table. I will test it tonight since I don't have that project with me atm. – Miljan Puzović Jan 19 '21 at 10:02
0

Here's a nice explanation to fix your problem. https://stackoverflow.com/a/325964/1500601. Explains the logic well to find such overlaps.

You can modify your query according to this logic. Probably the implementation would be like this:

SELECT * FROM bookings WHERE dp_date_start <= end_date AND start_date >= dp_date_end
ravi punjwani
  • 496
  • 4
  • 12
  • Thanks for your answer. Provided link helped me with possible solution. It's slightly different than your suggestion: `SELECT * FROM bookings WHERE dp_date_start <= end_date AND dp_date_end >= start_date` This is getting me correct results in improvised table. I will test it tonight on real project and let you know. – Miljan Puzović Jan 19 '21 at 10:12
  • Thanks for updating. Please let me know if you had to change the query. Glad that the explanation was helpful. – ravi punjwani Jan 20 '21 at 22:30
  • Something was wrong with these queries so I have ended up with Popeye's query. Maybe it was something to do with T-SQL tables (used in C#). – Miljan Puzović Jan 21 '21 at 12:19