Converting access datetime to mysql type
I am trying to convert an access datetime field to mysdl format using the following line:
select str_to_date('04/03/1974 12:21:22', '%Y %m %d %T');
Until I get the error, I don't get the expected result, instead I get this:
+---------------------------------------------------+
| str_to_date('04/03/1974 12:21:22', '%Y %m %d %T') |
+---------------------------------------------------+
| NULL |
+---------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
Access dates refer to this format:
06.10.2008 14:19:08
I'm not sure what I'm missing.
As a side question, I am wondering if it is possible when importing the csv file to modify the data in the column before? I want to replace the insert_date and update_date fields with my own dates, and I'm not sure if it would be easier to do this before import or after.
Many thanks for the help.
source to share
First of all, the str_to_date shown does not work because the format does not match the string. '% Y% m% d% T' will work if the date was something like '1974 04 03 12:21:22'
The correct format should be "% m /% d /% Y% T" (month / day / year). or "% d /% m /% Y% T" (day / month / year).
As far as access is concerned, it looks like it changes the above value. where the function / should work.
source to share
It is not clear to me which end of this you are using, end of access or end of MySQL, although it looks like you are trying to solve it with MySQL functions. If your problem is that you were exporting CSV from Access / Jet and not in the expected format, you may need to fix the CSV export from Access to use a format that MySQL understands.
If you have the ability to use an Access / Jet database, it might also be easier to set up an ODBC DSN for your MySQL database and then create linked tables in Access with which you can directly add data from Access MySQL Tables. The MyODBC driver will then take care of the data conversions.
source to share