CODEWITHSUNDEEP
mysql
Syco
Syco

Reputation: 821

DATE_FORMAT compare with string and number

I believed I was a proficient mysql user, but apparently I'm not.

I thought mysql would cast things before a compare, but this query baffled me:

SELECT
  DATE_FORMAT('2019-07-19 12:05:12', '%H') >= '11' AND DATE_FORMAT('2019-07-19 12:05:12', '%H') <= '7' AS 'str',
  DATE_FORMAT('2019-07-19 12:05:12', '%H') >= 11 AND DATE_FORMAT('2019-07-19 12:05:12', '%H') <= 7 AS 'num';

   str     num  
------  --------
     1         0

So how does the compare really works? What gets casted to what?

Upvotes: 0

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521904

In the first case, there actually doesn't appear to be any casting here at all, as DATE_FORMAT returns a string. The comparison being made is:

SELECT
    '12' >= '11' AND '12' <= '7' AS str

This true, because the text '12' is lexicographically greater than '11', and '12' is also "less" than '7' because the former starts with 1.

In the second case, the RHS of the inequalities are integers, and MySQL is casting the text numbers on the LHS to integers to do the comparison:

SELECT
    12 >= 11 AND 12 <= 7

This is false, which is the expected behavior for these inequalities using actual numbers.

Upvotes: 1

Related Questions