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?
-
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
Add a comment
|
1 Answer
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
