Date format

[Scott_Brasted via AccessMonster.com]

Greetings,

I am putting thisinquery because i think I will have to use a query to get my
result. I need to export an Accwss 2000 table to mySQL. The table has a field
that is a date. Access uses 12/24/2010. mySQL uses 2010-12-24. I need to
change the Access table before I import into mySQL. Does anyone know haw to
do this?

Best,
Scott

 

[John W. Vinson]

Greetings,

I am putting thisinquery because i think I will have to use a query to get my
result. I need to export an Accwss 2000 table to mySQL. The table has a field
that is a date. Access uses 12/24/2010. mySQL uses 2010-12-24. I need to
change the Access table before I import into mySQL. Does anyone know haw to
do this?

You do not need to, nor should you, change the Access table.

Instead, create a Query based on the table. In that query, don't include the
datefield itself; instead, put in a calculated field by typing

ExpDate: Format([datefield], "yyyy-mm-dd")

to convert the date (internally stored as a number) into a text string that
MySQL will recognize. You can then export the Query (rather than the table
itself).

 

[Scott_Brasted via AccessMonster.com]

John,

Thank you, I knew it would be easy, but that was even better than easy. I
have filed it in my toolkit for sql. It worked a charm.

Best,
Scott

Greetings,

[quoted text clipped - 3 lines]

change the Access table before I import into mySQL. Does anyone know haw to
do this?

You do not need to, nor should you, change the Access table.

Instead, create a Query based on the table. In that query, don't include the
datefield itself; instead, put in a calculated field by typing

ExpDate: Format([datefield], "yyyy-mm-dd")

to convert the date (internally stored as a number) into a text string that
MySQL will recognize. You can then export the Query (rather than the table
itself).