sqlmysqlsql-date-functionssqldatetime

SQL behaviour when using string literal vs using date literal

Suppose I have this two queries:

  1. SELECT * FROM payments WHERE created < DATE '1967-01-01'
  2. SELECT * FROM payments WHERE created < '1967-01-01'

And created field is a date field.

I would like to know if in query 2) is comparing two dates like in 1) and is not comparing two strings lexicographically.

Thanks!

Solution

  • Mysql treats both formats as dates, not as strings.

    As mysql manual on date and time literals say:

    Standard SQL requires temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.

    DATE 'str' TIME 'str' TIMESTAMP 'str' MySQL recognizes but, unlike standard SQL, does not require the type keyword. Applications that are to be standard-compliant should include the type keyword for temporal literals.

    ...

    MySQL recognizes DATE values in these formats:

    As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format.