converting access datetime type to mysql type

Question

Hello,

I am trying to convert an access datetime field to a mysdl format, using the following string:

select str_to_date('04/03/1974 12:21:22', '%Y %m %d %T');

While I do not get an error, I do not 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)

The access dates are in this format:

06.10.2008 14:19:08

I am not sure what I am missing.

As a side question, I am wondering if it is possible when importing a csv file to change the data in a column before? I want to replace the insert_date and update_date fields with my own dates, and I am not sure if it would be easier to do this before importing or after.

Many thanks for assistance.

Answer 1

First things first, the str_to_date shown doesn't work because the format doesn't match the string. '%Y %m %d %T' would 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 time). or '%d/%m/%Y %T' (day/month/year time).

As for access, it looks liks changing the above to use . where the / is should work.

Answer 2

Your syntax for the function is off.

Try:

select str_to_date('04/03/1974 12:21:22', '%m/%d/%Y %T');

The second parameter is telling the function where the parts of the dates are located in your string.

For your access question:

select str_to_date('06.10.2008 14:19:08', '%m.%d.%Y %T');

Answer 3

It's not clear to me which end of this you're using, the Access end or the MySQL end, though it looks like you are trying to solve it with MySQL's functions. If your problem is that you've exported CSV from Access/Jet and it's not in the expected format, then maybe you need to fix the CSV export from Access to use a format that MySQL understands.

If you have the ability to use the Access/Jet database, it might also be simpler to set up an ODBC DSN for your MySQL database, and then create linked tables in Access, to which you can then directly append data from the Access tables into your MySQL tables. The MyODBC driver will then take care of the data conversions for you.