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

Question

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 is all 00:00:00 anyway.

How can I do this in MySQL?

Thanks.

Answer 1

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.

Answer 2

Cast it as a DATE:

select DATE(my_date_time)

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

Answer 3

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;