MYSQL Query to return rows BETWEEN two DATETIME fields

Question

I have read many posts with answers concerning Mysql queries using BETWEEN none of which I can get to work with my data. In my case, I have a data table which has two cols both of which hold data as a DateTime. I am trying to right a query that returns a row(s) of data WHERE RoomToDateTime is BETWEEN two other DateTime fields. Example:

RoomToDateTime = "2018-09-10 17:00:00"

$FromDateTime   = "2018-09-09 08:00:00" //User selected date and time
$ToDateTime     = "2018-09-10 16:00:00" //User selected date and time

The query:

SELECT *
FROM Conf
WHERE RoomToDateTime BETWEEN '".$FromDateTime ."' AND '".$ToDateTime."'

This query returns no data when I know that there is a record where RoomToDateTime does contain "2018-09-09 10:00:00".

This works with dates only (excluding the time part). Can anyone see why the query does not work or am I going about this completely wrong.

Many thanks in advance for your time and helpful comments.

ScaisEdge · Accepted Answer · 2018-09-09 11:28:57Z

2

you should use str_to_date

  "SELECT * 
    FROM Conf 
    WHERE RoomToDateTime 
        BETWEEN str_to_date('".$FromDateTime ."', '%Y-%m-%d %T' )  
            AND str_to_date('".$ToDateTime."',  '%Y-%m-%d %T' )"

try this way with proper date time

  "SELECT * 
    FROM Conf 
    WHERE RoomToDateTime 
        BETWEEN str_to_date('2018-09-09 08:00:00', '%Y-%m-%d %T' )  
            AND str_to_date('2018-09-10 16:00:00', '%Y-%m-%d %T' )"

anyway you should not use php var ins sql string .. using these vars you are at risk for sqlijection ... for avoid this take a look at your php mysql driver for prepared statements and binding param

edited Sep 9, 2018 at 11:28

answered Sep 9, 2018 at 11:17

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

5 Comments

DCJones Over a year ago

thanks for your reply. have change the query to "SELECT * FROM Conf WHERE RoomToDateTime BETWEEN STR_TO_DATE('".$FromDateTime ."', '%Y-%m-%d %T' ) AND STR_TO_DATE('".$ToDateTime."', '%Y-%m-%d %T' )" but this still does not return any rows.

ScaisEdge Over a year ago

answer updated with a suggestion (check for your real content $fromDateTime and $toDateTime

DCJones Over a year ago

You were right, I did have the wrong content for $fromDateTime. Thanks to yur help this now works. Again, many thanks for your time.

DCJones Over a year ago

Hi, just one issue. If the year,month and day part of the "$FromDateTime" i.e "2018-09-09" and the "$ToDateTime" is i.e "2018-9-10" the query does not return any data, just the word "false". However, if "$ToDateTime" i.e "2018-9-11" the query returns data. I was expecting any record where RoomToDateTime is between the "$FromDateTime" and the "$ToDateTime". Can you see why this would be the case.

ScaisEdge Over a year ago

First if you use 2018-09-09 then you must use the proper mask %Y-%m-%d and second if have not the time part remember that the time ise assigned at 00:00:00

dognose · Accepted Answer · 2018-09-09 15:01:51Z

0

With the date format you provided, mysql will perfectly understand the <= and>= operator. (Just to outline an option):

SELECT *
FROM Conf
WHERE 
   RoomToDateTime >= '".$FromDateTime ."' AND 
   RoomToDateTime <= '".$ToDateTime."'

answered Sep 9, 2018 at 15:01

dognose

21k9 gold badges67 silver badges108 bronze badges

Collectives™ on Stack Overflow

MYSQL Query to return rows BETWEEN two DATETIME fields

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related