MYSQL Select between start end date date format d-m-Y [duplicate]

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
– Vladimir, Commented Jan 24, 2018 at 9:33
@DeadManAlive, how dare you?!? I wrote my answer in 2 minutes after the question, the above links were added AFTER my answer... — user2342558
– user2342558, Commented Jan 24, 2018 at 9: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
– Abdulla Nilam, Commented Jan 24, 2018 at 9:57

user2342558 · Accepted Answer · 2018-01-24 11:10:21Z

2

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.

edited Jan 24, 2018 at 11:10

answered Jan 24, 2018 at 9:31

user2342558

6,8046 gold badges42 silver badges67 bronze badges

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

Comments

Kannan K · Accepted Answer · 2018-01-24 09:32:10Z

0

Try this:

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

answered Jan 24, 2018 at 9:32

Kannan K

4,4891 gold badge15 silver badges27 bronze badges

2 Comments

Gurdt Over a year ago

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

Kannan K Over a year ago

@Gurdt please post your mysql error.

NikuNj Rathod · Accepted Answer · 2018-01-24 09:37:23Z

0

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.

edited Jan 24, 2018 at 9:37

NikuNj Rathod

1,6381 gold badge19 silver badges29 bronze badges

answered Jan 24, 2018 at 9:31

Léo R.

2,7161 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

MYSQL Select between start end date date format d-m-Y [duplicate]

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related