Mysql strip time component from datetime

Question

I need to do a date comparison in Mysql without taking into account the time component i.e. i need to convert '2008-11-05 14:30:00' to '2008-11-05'

Currently i am doing this:

SELECT from_days(to_days(my_date))

Is there a proper way of doing this?

Answer 1

Yes, use the date function:

SELECT date(my_date)

Answer 2

In PostgreSQL you use the TRUNC() function, but I'm not seeing it for MySQL. From my brief Googling, it looks like you'll need to cast your DATETIME value to be just DATE.

date_col = CAST(NOW() AS DATE)

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

Answer 3

select date(somedate) is the most common.

If you need to accommodate other formats, you can use: SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');