date conversion

[logic]

hello gurus,

I am getting date(from xml) in a format 4/5/2005 11:11:00 AM
I have to load it to target oracle table... the type for the field to be loaded in target is date. My problem is that the incoming date has AM ....also at present the
datepart of the string is in the format d/m/yyyy...... but in future it is
bound to become dd/mm/yyyy....I tried iconv... oconv... tried doing left
on the string and pulling just the date part(had the problem... format
d/m/yyyy...to become dd/mm/yyyy)and then converting it...all useless...any
ideas..suggestions.
Thanks.
Ash.

[chulett]

Your target stage in the job should be an OCI stage. Your target field should be declared as a type of Timestamp in the OCI stage. Then you need to use Iconv/Oconv to get it in the format that the stage is expecting:

Code: Select all

YYYY-MM-DD HH24:MI:SS

People typically build routines to automate this so they can be used as standard routines across any jobs that need to handle things like Oracle DATE fields.

[logic]

Hi Craig,
Thanks for the help. Yes I already tried to do Iconvbut the problem is that I have to get rid of the AM part in the incoming
(xml) date also the date is in format d/m/yyyy hh:mm:ss AM and in future it will become dd/mm/yyyy hh:mm:ss AM. I tried to trim to get the required format but the error is string to date type conversion unsucessfull
Thanks again.
ash.

[chulett]

Still need help with this? Look at some of the Date routines supplied in the 'sdk' section - there is alot of code you can 'borrow' from there.

You should be able to handle d/m/yyyy and dd/mm/yyyy with no problem with one conversion. You need to do something similar for the time portion as well... substring it off and do the same Iconv/Conv thing with it, this time using the MT conversion code rather than the D code - 'MTHS' on the Iconv and 'MTS' on the Oconv.

There are examples in the online help under Conversion Codes for all of this.

[lclapp]

Ans = FIELD(Arg1," ",1,1) This will give you back the date portion without have to handle checking for the '/' and such. It is shown as a function but would work in a derivation if all you need is the date value in the xml format. You can then Iconv and Oconv to get the right format.

[clshore]

Or,
since your target is Oracle, let the DB engine do the dirty work.
AFAIK, the date format of your example XML string is already the Oracle default, but you should use this date format in the Sql to explicitly convert the string to a date.

to_date(yourXmlDate, 'MM/DD/YYYY HH:MI:SS AM')

Carter