I started on already running web app today where it is required now to sort documents by datetime. the timestamp in table is “2011-Aug-01 11:10” and its datatype is varchar.
i noticed above today as i was not getting latest info from table. then i checked the type and it is varchar. i can’t change it straight away to timestamp/datetime as format is not correct. is there any kind of update query which i can run on column to make it right format then change it to datetime data type?
is it possible. ?
if it can’t be done in mysql i am thinking about doing following way.
- go through each row
- change time format from “2011-Aug-01 11:10” to “2011-08-01 11:12:13” format
- change mysql datatype to datetime [i think mysql won’t complain as time is in right format]
now what format is exactly similar to mysql datetime datatype. is date(‘c’) the right choice. ?
there are already 5000+ rows in it.
thanks in advance