Page 1 of 1

MYSQL VarChar to Date Convertion

Posted: Thu Feb 19, 2009 12:09 am
by rajaboys
I have a database in which the date is stored in varchar field in a
following format: date("F, d, Y"); ex: May, 27, 2008, now the problem
is that I want to change that field into mySQL date field as well as
convert my older dates into MySQL date format i-e Y-m-d (2007-08-06).

Thanks in Advance
Raja :cry:

Re: MYSQL VarChar to Date Convertion

Posted: Thu Feb 19, 2009 1:40 am
by Eran
Create a new date-type column in the same table. Then run an update query that performs STR_TO_DATE conversion:

Code: Select all

UPDATE table_name SET new_date_column = STR_TO_DATE(old_date_column,'%M, %d, %Y');
After that you can drop the old date column (be sure to backup your table in advance in case the format isn't exactly correct).

http://dev.mysql.com/doc/refman/5.1/en/ ... tr-to-date
http://dev.mysql.com/doc/refman/5.1/en/ ... ate-format