How to compare a date to a string

user_61335 0
Viewed 60

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.
enter image description here

enter image description here

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.enter image description here

enter image description here

dbeaver mysql
3 Answers

I turns out it was a DBeaver issue. I changed the MySQL driver setting noDatetimeStringSync to true. The default it false. Not sure why it just started doing this

user_613350

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?

user_124928900

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);
user_149984870
Related