I'm using MySQL 5.6 and I have datetime stamps like one of this: 2015年01月28日 08:30:02:843000. Currently, they are stored as strings. I tried to alter the table to make this into datetime objects but the command failed. I'm rough with SQL, what would be the query to convert these to datetime objects? Do I need to remove the milliseconds somehow? Or convert the last colon to a period?
ApproachingDarknessFish
14.4k8 gold badges42 silver badges81 bronze badges
asked Jan 30, 2015 at 19:51
nicholas.reichel
2,2707 gold badges22 silver badges30 bronze badges
-
2What RDBMS are you using?Mureinik– Mureinik2015年01月30日 19:53:43 +00:00Commented Jan 30, 2015 at 19:53
-
well, if it's sql server this would help stackoverflow.com/questions/19025192/…Madison– Madison2015年01月30日 19:58:15 +00:00Commented Jan 30, 2015 at 19:58
-
possible duplicate of Unable to convert varchar to datetime in MySqlNathan Hughes– Nathan Hughes2015年01月30日 21:03:15 +00:00Commented Jan 30, 2015 at 21:03
-
1I'd guess you're reinforcing the general principle that it's a good idea to store the data in its natural format (i.e. a date) let the import and export procedures handle converting to and from the source and usage. It also helps the database manipulate the data more efficiently, in most cases.dkretz– dkretz2015年01月31日 05:55:00 +00:00Commented Jan 31, 2015 at 5:55
-
Yea I only have a couple days of data, I'll probably just scrap it and fix it. Thanks!nicholas.reichel– nicholas.reichel2015年01月31日 05:56:33 +00:00Commented Jan 31, 2015 at 5:56
1 Answer 1
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date 'STR_TO_DATE(str,format)'
Should do the trick for you. What you want to do A) Create New Field (date) B) UPDATE table set new field convert(old field) C) You're done
Sign up to request clarification or add additional context in comments.
Comments
lang-sql