Convert date/Time string to date valid in an db import in MS Access

SteinerKD has asked for the wisdom of the Perl Monks concerning the following question:

I've got my script working fine now collecting data for me, however I fail to import it into my DB and it seems the date fields are the culprit (MS Access hissyfits and says "Type Conversion Failure" for ever date/time field.

All time references grabbed are in this format "Jul 24 00:33" and needs to converted to something Access will accept. Is there any relatively easy way to accomplish this so it can be done on the fly when I collect those fields before I store them to file?

Comment on Convert date/Time string to date valid in an db import in MS Access

Replies are listed 'Best First'.
Re: Convert date/Time string to date valid in an db import in MS Access by bobf (Monsignor) on Aug 05, 2010 at 03:41 UTC
Super Search How to insert local time and date to MS-Access database using PERL program (the approach that I know about, for obvious reasons) Perl and Microsoft Access (might be something in here, too)	[reply]
Re: Convert date/Time string to date valid in an db import in MS Access by aquarium (Curate) on Aug 05, 2010 at 01:26 UTC
It's been a long time so can't remember the full details. In Access you can setup a import format file and use it through the import wizard in Access. Otherwise can't remember the default format but i'm sure Access Help lookup on import will help. If you're going to do this on perl side, much better to use solid date/time module rather than writing own date/time functions, as there are a few gotchas, and the modules can work with many different formats for date/time. Also noticed that Year is missing from the date. Unless you're absolutely and positively sure that you'll never need the year from the date, it should be there to start with. Assuming current year during import and other such things are likely to bite back. the hardest line to type correctly is: stty erase ^H	[reply]
Re: Convert date/Time string to date valid in an db import in MS Access by Anonymous Monk on Aug 05, 2010 at 00:47 UTC
Yes :) Super Search	[reply]