Search code examples
mysqldatetimesubstring

Remove milliseconds in a datetime field

I converted a database from MS SQL to MySql using workbench. There is a table that has a column called ActivityDate (datetime(6)) . For some reason, when that column got converted it has a dot in the date like (2013-05-03 11:20:20.420000) .

I want to remove the .420000 or whatever number is after the dot. I tried doing SUBSTRING_INDEX(ActivityDate,'.',1) but that didn't work, the last digits would just be .000000

I also tried UPDATEalerts.activitylogSETActivityDate= date_format(ActivityDate, '%Y-%m-%d %H:%i') WHEREactivitylog.ActivityLogID= 5;

And same issue... I get .000000 at the end

How can I do this?

Solution

  • Simply change the data type of the column to exclude the fractional part.

    ALTER TABLE alerts.activitylog MODIFY ActivityDate DATETIME;

    The type datetime(6) means 6 digits after the decimal point.

    See the MySQL date and time fractional support documentation for details.