CODEWITHSUNDEEP
mysql
Govind_das
Govind_das

Reputation: 67

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

I've view ReportView

enter image description here

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 :

enter image description here

What actually is happening?

Upvotes: 1

Views: 449

Answers (1)

Barmar
Barmar

Reputation: 781004

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');

Upvotes: 2

Related Questions