CODEWITHSUNDEEP
mysqlsql
user1400
user1400

Reputation: 1409

sort table with timestamp field without considering time of day

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).

Upvotes: 7

Views: 4973

Answers (3)

Ankit
Ankit

Reputation: 2753

select columns
from table_name
order by date_format(date_column, '%Y-%m-%d')

Upvotes: 3

mu is too short
mu is too short

Reputation: 434835

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

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

Upvotes: 3

zerkms
zerkms

Reputation: 255025

ORDER BY date(mydate)

but it will cause fullscan.

Upvotes: 10

Related Questions