Mark Brown's Blog

As you can see in my Blog entry on how to import a Microsoft Access Database to MySQL , I ran into a small problem on converting all the dates from the format of mm/dd/yyyy to MySQL Date Format of yyyy-mm-dd.

Thanks to all the help I recieved from Guelphad on the MySQL Newbie forum in this post, I got all the dates converted and am good shape.

 Here's how I converted the date formats on Guedlphad's Advice.

 In my table(misequip) , I created a new column with a data type of DATE with a name (dt_deployed) similar to the column that contained my dates imported from Access (date_deployed) in string format.  If you'll remember, I imported all the dates as a datatype of VARCHAR just to get them into the database.

I then ran this command:

mysql> UPDATE misequip SET dt_deployed =  str_to_date(date_deployed, '%m/%d/%Y');

What that command does is UPDATE's the misequip table, SET's the new column dt_deployed to equal the out put of the MySQL function STR_TO_DATE that converts Strings to Dates.

Once I ran this command, I looked at the my database and all the dates were converted.  Worked like a champ.  I then dropped my original column, then renamed my new column to match the old name, date_deployed.

 We will now be albe to do calculations on these dates now since they are in DATE format to figure out when warranties expire and stuff like that.

 Hope this help some of you newbieMySQL guys out there,

Mark