Querying dates: is "dateval LIKE '2014-01-01%'" a best practice? [duplicate]

Question

This answer suggests using greater/less than to query for the dates matching a given day. But one of the veterans of my company told me to use LIKE '2014-02-06%'.

What's the best practice? Is there any significant advantage of one over the other?

Answer 1

When you use LIKE in this context, you are (implicitly and invisibly) typecasting the DATE, TIMESTAMP, or DATETIME column value to a string, then comparing the string to the pattern you mentioned. The typecasting operation definitively defeats the use of any index on the column value. So does any function applied to column values in a WHERE clause.

So, the result your colleagues get is correct. But they get it much more slowly than they need to, because they use a full table scan instead of an index range scan.

By the way, BETWEEN works correctly for DATE columns but not for DATETIME or TIMESTAMP columns. To choose one day's dates from one of the latter two, you need

WHERE dateval >= '2014-02-06'
  AND dateval <  '2014-02-06' + INTERVAL 1 DAY

The problem is that

WHERE dateval BETWEEN '2014-02-06'  /* wrong! */
                  AND '2014-02-06' + INTERVAL 1 DAY

picks up the dateval items exactly at midnight on the day after the day you want. Both formulations use a range scan on the index, which makes them fast. But BETWEEN can get wrong results.

Answer 2

It's not a good idea if you query with LIKE on date field it will compute string representation of a date for all dates. But if you use BETWEEEN operator it will just compare int value of timestamp to stored data.