SQL behaviour when using string literal vs using date literal

Question

Suppose I have this two queries:

SELECT * FROM payments WHERE created < DATE '1967-01-01'
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!

I have deleted my answer because I wrongly thought that the question was about SQL Server. The MySQL rules are very different. dev.mysql.com/doc/refman/8.0/en/type-conversion.html — Ben
– Ben, Commented Oct 15, 2023 at 23:30
You should probably ensure you use a CAST or CONVERT to make sure that the comparison is done in the way you want. — Ben
– Ben, Commented Oct 15, 2023 at 23:31
Implicit cast as date, but I'd still recommend using a proper date literal, i.e. query 1. — jarlh
– jarlh, Commented Oct 16, 2023 at 7:11

Shadow · Accepted Answer · 2023-10-15 23:42:42Z

1

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.

answered Oct 15, 2023 at 23:42

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL behaviour when using string literal vs using date literal

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related