Alicia Posted January 4, 2013 Share Posted January 4, 2013 Hi guys, Anyone know a quick fix how I can change a column value from US datetime to UK in all records? e.g : 25/12/2010 23:59:00 to 12/25/2010 in the database Currently the var type for this column is varchar Please advise. Alicia Quote Link to comment Share on other sites More sharing options...
Barand Posted January 4, 2013 Share Posted January 4, 2013 The best thing to do is change the format to MySQL DATE format (yyyy-mm-dd) then you can take advantage of the dozens of built-in datetime functions and it also allows you to compare and sort the dates correctly. Store dates for functionality in the DB, not for prettiness. Add a date column, type DATE and run an UPDATE query using mysql's STR_TO_DATE function to convert from your format. Quote Link to comment Share on other sites More sharing options...
Alicia Posted January 4, 2013 Author Share Posted January 4, 2013 If I need the time as well so shall I use datetime? so what you recommended is I add a new column with datetime format, then use str to datetime function to convert the value and update the generated value into the newlly added column? Quote Link to comment Share on other sites More sharing options...
Barand Posted January 4, 2013 Share Posted January 4, 2013 yes yes. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.