I have several tables that won't import the dates correctly into mySQL because they are formatted incorrectly. My database tables all show the dates MM/DD/YYYY and it seems that mySQL wants them in this format YYYY-MM-DD.

The tables import, but the dates are all zeroed out 0000-00-00

I surely am not the first person to have this problem.

Thanks,

Kirk

I've run into this problem myself before. MySql is particular and will not except the data if it is not formatted just so.

You are correct the proper format is YYYY-MM-DD and so the only solution is to format the date before importing it. You can do this easily in Excell. Just open your csv file in Excel and format that column correctly. Save the file as a CSV and then do the import.

Of course I don't know if you are doing a custom solution or using an import tool.

Another more advanced option would be to import the date into a varchar field and use mysql functions to grab the value and change the format on import into the date field. In the case you are using load data local infile you could do this (assuming you used a field called varcharDate to import the raw date string and you have a field called date for the data field).



The only downside to this is that you now have two columns of similar data. However it's important to store dates as a date object so you can reap the power of mysql and this eliminates the need to do any external editing of the data prior to import.

see

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

for more.