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

More Vista 64 Weirdness

Well I thought I had it all together. The Adobe printer with Acrobat using Access cannot prompt for a file location, when I set that option the process hangs… If I leave it going to the default .Documents folder all runs ok.

But within Access the printer option dialog started appearing then disappearing depending on mouse (touchpad) operation,,,particularly when trying to set Acrobat printer options/ doesn’t give me a lot of confidence…I really don’t have that much time to devote to ironing all the tweeks out. So far Neither Microsoft not Adobe are offering to pay!

I had heard that Vista 64 was running regular Win 32 bit apps fine, Your mileage may vary!!! I don’t consider either Acrobat  v8 nor Access v2003 to be terribly exotic!