Getting rows between date range in mysql

Question

I am having a serious issue I can not figure out. I am trying to return rows from MySQL database that have a Start date between two given dates and an End date as well. Here is my php code to query the database:

$getRooms = mysql_query("
    SELECT * 
    FROM Tarifas 
    WHERE Start BETWEEN '2013-01-10' AND '2013-01-13' 
    AND   End   BETWEEN '2013-01-10' AND '2013-01-13'
");

Start and End are set up as DATE fields

my database is set up as follows:

ID | RoomId | Start      | End
--------------------------------------
4  | 34562  | 2013-01-09 | 2013-10-23

If anyone can help me figure out why this is not working, it would be greatly appreciated!!

Unless the data you have provided is incorrect, you have an end date in the table as 2013-10-23 but you are looking for end dates BETWEEN '2013-01-10' AND '2013-01-13' -- that data doesn't match so you will get no results. — Taryn
– Taryn, Commented Sep 9, 2012 at 15:52
What is the type of the Start and End columns? (Can you give the statement that created the Tarifas table?) — alexis
– alexis, Commented Sep 9, 2012 at 15:54
Are you checking for date conflicts (e.g. when the whole date range or part of it lies inside a date range stored in database)? — Salman Arshad
– Salman Arshad, Commented Sep 9, 2012 at 15:54

Salman Arshad · Accepted Answer · 2014-01-13 07:30:15Z

3

Looking at your query and sample data, perhaps you are looking for a SQL Query to Find Overlapping or Conflicting Date Ranges. The query would be:

SELECT *
FROM Tarifas 
WHERE '2013-01-13' >= `Start` AND `End` >= '2013-01-10'

2013-01-13 is greater than 2013-01-09 -- and -- 2013-10-23 is greater than 2013-01-10 so Tarifas #4 will be returned since it conflicts/overlaps with the specified dates.

edited Jan 13, 2014 at 7:30

answered Sep 9, 2012 at 15:56

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anton Over a year ago

He only stated that " I am trying to return rows from MySql database the have a Start date between two given dates and an End date as well."

Salman Arshad Over a year ago

I just inferred this from the sample data and query (and the little knowledge that I have about booking "rooms" and rateplans). The input range is 2013-01-13 thru 2013-01-13 and he PROBABLY wants row id #4 to be returned.

luv2code Over a year ago

This worked well, I just had to modify it a bit to WHERE start_date<= 'mydate' AND end_date >= 'mydate' Thank you very much

Collectives™ on Stack Overflow

Getting rows between date range in mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related