How to compare a date to a string-CodePudding

I have a mysql query where I am trying to use a string in the where clause.

select s.schedule_date 
from schedule s 
WHERE DATE(s.schedule_date) = '2022-06-04'

and it does not work.
I have also tried s.schedule_date = '2022-06-04' DATE(s.schedule_date) = DATE('2022-06-04')

But nothing seems to work. Most articles I have seen say the where in the SQL example should work.

select
    s.schedule_date,DATEDIFF(s.schedule_date,"2022-06-04")
from
    schedule s
WHERE
    s.schedule_date >= "2022-06-03" and
    s.schedule_date <= "2022-06-05";

Give me 1 when it should be 0.

CodePudding user response：

You can try using the MySQL STR_TO_DATE function: it converts a string to a date, given the input format, that in your case should be %Y-%m-%d.

SELECT s.schedule_date 
FROM schedule s 
WHERE DATE(s.schedule_date) = STR_TO_DATE('2022-06-04', '%Y-%m-%d')

Does it solve your problem?

CodePudding user response：

You could use the CAST-Function, to convert your strings into dates. Of course, CAST can also be used for any other transformation (INT to STRING, STRING TO CHAR(n), ...). In your case, the query should look like this:

    SELECT
    s.schedule_date, DATEDIFF(s.schedule_date, CAST("2022-06-04" AS DATE))
    FROM
    schedule s
    WHERE
    s.schedule_date >= CAST("2022-06-03" AS DATE) AND
    s.schedule_date <= CAST("2022-06-05" AS DATE);