MYSQL VarChar to Date Convertion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rajaboys
Forum Newbie
Posts: 5
Joined: Wed Dec 24, 2008 7:13 am

MYSQL VarChar to Date Convertion

Post 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:
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MYSQL VarChar to Date Convertion

Post 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
Post Reply