MYSQL Select between start end date date format d-m-Y

Question

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

Please check type of "reservationStartDate" and "reservationEndDate" columns in SQL database — Vladimir, Jan 24 '18 at 09:33
@DeadManAlive, how dare you?!? I wrote my answer in 2 minutes after the question, the above links were added AFTER my answer... — user2342558, Jan 24 '18 at 09:55
@user2342558 so whats the matter?? and what you mean by **posts above were added**. The links which i set to duplicates are older than months/years. Not in `2 minutes` :P — Abdulla Nilam, Jan 24 '18 at 09:57
THIS question (this page) is posted 2 minutes after my answer. -_- — user2342558, Jan 24 '18 at 10:02

user2342558 · Answer 1 · 2018-01-24T11:10:21.760

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.

score 0 · Answer 2 · edited Jan 24 '18 at 09:37

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.

score 0 · Answer 3 · answered Jan 24 '18 at 09:32

0

Try this:

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

answered Jan 24 '18 at 09:32

Kannan K

4,411
1
11
25

SELECT * FROM reservations WHERE reservationStartDate BETWEEN STR_TO_DATE('01-01-2017', '%d-%m-%Y') AND reservationEndDate STR_TO_DATE('01-01-2019', '%d-%m-%Y') This gives an error. I want to get START and END date – Gurdt Jan 24 '18 at 09:37
@Gurdt please post your mysql error. – Kannan K Jan 24 '18 at 09:40

MYSQL Select between start end date date format d-m-Y

3 Answers3