|Problems? Is your data what you think it is?|
Computing Oracle date format from a date stringby vladb (Vicar)
|on Aug 23, 2002 at 19:05 UTC||Need Help??|
vladb has asked for the
wisdom of the Perl Monks concerning the following question:
I'm presently dealing with an application where an external vendor sends me xml data that gets then parsed by my Perl script and saved in our database for future retrieval and etc. One of the xml fields is date stamp for the data.
Unfortunately, defying my pleas for consistent date formats, the vendor keeps sending me data with date stamp strings of varying 'composition'. On one occassion, I'd get '2002-08-23 11:39:24.0', or 'Fri Aug 23 12:14:04 2002', or ... This complicates my task of formating these date strings into something that Oracle would accept (when inserting data into the database via an INSERT sql statement). In my parser configuration, I set expected date format string to 'DY MON DD HH24:MI:SS YYYY'. So, my parser would affectively work only on data files that have a date stamp string of matching format. Otherwise, Oracle would complain since the TO_DATE would always fail. For example, TO_DATE('2002-08-23 11:39:24', 'DY MON DD HH24:MI:SS YYYY') is bound to fail; whereas this should work TO_DATE('2002-08-23 11:39:24', 'YYYY-MM-DD HH24:MI:SS').
So, my question is whether anyone of you knows of any Perl method or module (I couldn't find) that would allow me to build a Oracle date format string from (such as 'YYYY-MM-DD HH24:MI:SS') a date string (such as '2002-08-23 11:39:24')?
Update: I can't predict all valid date format variations that the vendor may use. That was the only reason I asked if there's any module that could do something like this, so that I won't have to ;-).
As I look at it now, I could use Class::Date to create a date object from vendor date string and then reformat it in one format that Oracle would understand. This is actually a reverse look at my initial 'question'.