Need help with str_to_date function

[dcnguyen]

Here's my problem...I have a database full of dates that were entered in this format: 1/1/2005 0:00:00

This field is currently in varchar...how would I go about changing these dates to the proper format using str_to_date? I understand how that function works, but unfortunately, my mysql knowledge is limited to doing single retrieval queries...

I do know how to grab the date value, use php to convert it, and then insert it back into the row...but there's got to be a more efficient way with just mysql, right?

 

[elsenorjose]

Are you trying to change the data type for that column so ALL values in there are converted? If so, you need to modify the table itself with the 'alter table' command. This link will provide details on the proper syntax.

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

 

[dcnguyen]

No...I tried that, using the alter command to change the field datatype to date and datetime...But it converts everything to 0000-00-00 00:00:00

 

[elsenorjose]

I don't know if you can set a column format using str_to_date. If you just want to convert the values to dates when you run a query, here are some syntax examples:

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'

Some more reading on date functions:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

[dcnguyen]

Well, I wouldn't be setting a column format with str_to_time...what'd i'd like to do is convert the value in the bad datefield into a proper date, then insert it into another date column which is set to date type. Is there a way to do that in one query?

 

[elsenorjose]

I'm assuming your table has a unique identifier for every record.

Try:

update table YourTable T1
set GoodColumn =
(select str_to_date(bad_colum)
from YourTable T2)
where t1.id = t2.id

I haven't read about anything in MySQL that prevents you from updating a column based on a subselect so this should work.

 

[dcnguyen]

Thanks, like I said...my mysql knowledge is pretty limited...

 

[elsenorjose]

No worries. The best way to learn is just to try