How does MySQL compare datetime to date?

Question

Can someone please explain how comparisons between date and datetime are evaluated? When a time is specified on one side but not the other, what is it comparing against? For instance:

SELECT '2018-01-01 00:00:00' > '2018-01-01'

is 1, but

SELECT '2018-01-01' > '2018-01-01'

is 0

I thought that without a time specified the dbms would assume 00:00:00 but apparently that is not the case. Why does my first comparison above evaluate as true while the second does not?

Answer 1

Those aren't dates, they're strings - you might as well be running SELECT 'A' > 'B'. Convert them to DATEs:

SELECT CAST('2018-01-01 00:00:00' AS DATE) > CAST('2018-01-01' AS DATE)

or to DATETIMEs:

SELECT CAST('2018-01-01 00:00:00' AS DATETIME) > CAST('2018-01-01' AS DATETIME)

Casting isn't necessary if you're comparing native MySQL dates coming from a DATE, DATETIME, or TIMESTAMP column - MySQL knows what to do with those. It's only an issue here because you've given it raw strings.