Date Conversion to MySQL From Access/ SQL server

It took me a while to figure out how to convert dates from the format produced by Microsoft Access or Microsoft SQL server to the date format required by MYSQL. It is essential if you want to do any date manipulation.

My data comes as an ascii text file from a MSQL server which I import using Heidi SQL, a neat free package that can be found here. As far as I can tell it’s the only freeware supporting imports to MYSQL with some graphical help. It also has a number of other nice features and is under active development. In general it is better suited for me that the MYSQL workbench (which is pretty useless in the “free” edition), and for the time being I don’t feel like spending even the small $ to get the full edition. For the import, define the fields as terminated by \t and define the lines as terminated by \r\n .Ignore one line (to skip the headers, since you have to import into a defined table with field names. It took me a while to find those escape chars!!! not intuitive for someone accustomed to Microsoft!!

Anyhow once you get the dates into the database they are simply text and  may be in various forms, long or short. The fields Month Day Year are in different order.. so here is a small piece of code that changes the Microsoft format into a MYSQL one, you must have defined the destination field (DATE_MODIFIED in my example) in your database as a date field. The code uses two nested functions, The substring_index, and the STR_TO_TEXT to get the job done. So you can look up the references to see how it does its’ work. It handles nulls and blanks in the source field.

Old hands may think how simple, but I was rather pleased with my 1st, and so far only line of MYSQL code!

This takes the text contents of SDATE_MODIFIED (a string field) and puts a proper MYSQL date into DATE_MODIFIED (a date field).

Update ARESIMPORT set DATE_MODIFIED= STR_TO_DATE(substring_index(SDATE_MODIFIED,” “,1),’%c/%e/%Y’);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s