php – MYSQL Select between start end date date format d-m-Y-ThrowExceptions

Exception or error:

If i do this in MYSQL it works:

    SELECT * FROM reservations
WHERE
reservationStartDate >= '2018-01-01' 
AND
reservationEndDate <= '2018-02-16'
AND
reservationSpace = 'kantoor-1'

So i want to make a function like this:

        public function checkReservations($startdate, $enddate, $workspace, $bind="") {
            //SELECT * FROM reservations WHERE reservationStartDate >= '2018-01-19' AND reservationEndDate <= '2018-02-01' AND reservationSpace = 'kantoor-1';
            // Date format = 2018-12-31
    $sql = "
            SELECT * FROM DATABASENAME.reservations
            WHERE
            reservationStartDate >= '".$startdate."' 
            AND
            reservationEndDate <= '".$enddate."'
            AND
            reservationSpace = '".$workspace."'
            "
            ;               
    $this->run($sql,$bind);
}       

And in php i use:

$test2 = $db->checkReservations('2018-01-01', '2018-02-16', 'kantoor-1');

echo '<pre>'.print_r($test2,true).'</pre>';

It show’s nothing… Im almost there btw i have change the format of the date to Y-m-d

How to solve:

You can use the STR_TO_DATE MySQL function:

SELECT * FROM reservations 
WHERE reservationStartDate 
BETWEEN STR_TO_DATE('22-01-2018', '%d-%m-%Y') 
        AND STR_TO_DATE('03-02-2018', '%d-%m-%Y')

BETWEEN and STR_TO_DATE

BETWEEN is the better form of reservationStartDate >= '22-01-2018' AND reservationStartDate <= '03-02-2018' (also in terms of speed) but it your case it works only with STR_TO_DATE converting the ‘d-m-Y’ string to a real date format to let MySQL to handle it.

DATE type field

The best thing you can do is to use a DATE type field to store a date, so you don’t need STR_TO_DATE anymore.

Answer:

Try this:

SELECT * FROM reservations WHERE reservationStartDate >= '22-01-2018' 
AND reservationEndDate<= '03-02-2018'

Answer:

if you need to select rows from a MySQL database table in a date range, you need to use a command like this:

SELECT * FROM table 
WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';

You can use STR_TO_DATE() to convert your strings to MySQL date values :

SELECT * FROM reservations 
WHERE reservationStartDate BETWEEN STR_TO_DATE('22-01-2018', '%d-%m-%Y') AND STR_TO_DATE('03-02-2018', '%d-%m-%Y')

However, you would be wise to convert the column to the DATE data type instead of using strings.

Leave a Reply

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