sort table with timestamp field without considering time of day

Question

I have a table in Mysql (firstname,lastname,data1,data2,...) that one field name is MYDATE and type of this field is timestamp. In this field, the date saved as (yyyy-mm-dd mm:ss:ms), and there are many records of this table.

I want write a select query that sort this table with (yyyy-mm-dd) and without considering (mm:ss:ms).

Answer 1

ORDER BY date(mydate)

but it will cause fullscan.

Answer 2

Just cast it to a date in your order by clause:

SELECT columns
FROM some_table
ORDER BY CAST(mydate AS date);