How can I convert datetime to date, truncating the times, leaving me the dates?

I have a field that's in datetime format when date would be better and more consistent with the rest of the database, so I want to convert. The time part开发者_Go百科 is all 00:00:00 anyway.

How can I do this in MySQL?

Thanks.

If you want this in a SELECT-Statement, just use the DATE Operator:

SELECT DATE(`yourfield`) FROM `yourtable`;

If you want to change the table structurally, just change the datatype to DATE (of course only do this if this doesn't affect applications depending on this field).

ALTER TABLE `yourtable` CHANGE `yourfield` `yourfield` DATE;

Both will eliminate the time part.

Cast it as a DATE:

select DATE(my_date_time)

That will truncate the time from it, leaving only the date part.

when using change we have to repeat the same name again for the field.now we can use MODIFY to alter the filed type.

ALTER TABLE `yourtable` MODIFY `yourfield` DATE;