mysql select query where date = ... not returning data

Question

I've view ReportView

I want to fetch the result having date 10-Feb-2020

I tried

    select * from ReportView where date = "10/02/2020";
    select * from ReportView where date = date_format("10/02/2020","%d/%m/%Y");
    select * from ReportView where date = str_to_date("10/02/2020","%d/%m/%Y");
    select * from ReportView where date_format(date,"%d/%m/%Y") = date_format("10/02/2020","%d/%m/%Y");
    select * from ReportView where str_to_date(date,"%d/%m/%Y") = str_to_date("10/02/2020","%d/%m/%Y");
    select * from ReportView where date = CAST("2020-10-02" AS DATE);
    select * from ReportView where CAST(date AS DATE) = CAST("2020-10-02" AS DATE);

Output is empty :

What actually is happening?

What if you try select * from ReportView where date = '10-02-2020' ? — The Impaler
– The Impaler, Commented Feb 10, 2020 at 21:06
@TheImpaler '10-2-2020' worked. How can I do it with '10/2/2020'? Date format from datepicker is d/m/Y — Govind_das
– Govind_das, Commented Feb 10, 2020 at 21:09
it is never a good idea the use SQL keywords as field names. — Bernd Buffen
– Bernd Buffen, Commented Feb 10, 2020 at 21:12

Barmar · Accepted Answer · 2020-02-10 21:12:32Z

2

You need to use str_to_date() to parse the incoming dates, then date_format() to format them in the way you have it in the table column.

select * from ReportView where date = date_format(str_to_date('10/02/2020',"%d/%m/%Y"), '%d-%m-%Y');

answered Feb 10, 2020 at 21:12

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

mysql select query where date = ... not returning data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related