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’);

More Vista Weirdness: Printing PDF on Vista

As posted earlier, I could not seem to get Acrobat 8 (latest patches) to print correctly from Access Yes I could print, after having followed Adobe’s debug script to re-run install/repair (from control panel) but only in black and white and without being able to specify file location or file replace…(program just hung)

About to give up, I installed some freeware, “bullzip printer” after a reboot, not specified in the documentation, it works like a charm, well it keeps defaulting to black and white but will do color when asked nicely and repetativley!

Why can’t Adobe get this right, they charge a premium price but support committment and bloat are going to kill them.

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!