DSXchange

Posted: **Thu Nov 19, 2009 5:38 pm**

Hello:

I get an external file that has "Record Date" as MM/DD/YYYY HH24:MM (11/19/2009 18:24) format.. The rows abort trying to load into oracle table due to the date/time format. In oracle that 'Record Date' is defined as Timestamp (YYYY-MM-DD HH24:MI:SS). Upon using search functionality here, I used Iconv/Oconv functions without any luck... I am home and cannot paste what I am doing...
Any pointers or code to help me convert the input column from "MM/DD/YYYY HH24:MI" to Oracle's "YYYY-MM-DD HH24:MI:SS" format is highly appreciated.

Thank You,
-Sam

Posted: **Thu Nov 19, 2009 5:46 pm**

For the date portion

Code: Select all

Oconv(Iconv(Left(InLink.Fieldname,10),"DMDY"),
"D-YMD[4,2,2]")

For the time portion

Code: Select all

Oconv(Iconv(Right(InLink.Fieldname, 5), "MT"),"MTS")

Concatenate these with a space between.

Code: Select all

Oconv(Iconv(Left(InLink.Fieldname,10),"DMDY"), "D-YMD[4,2,2]") : " " : Oconv(Iconv(Right(InLink.Fieldname, 5), "MT"),"MTS")

Posted: **Thu Nov 19, 2009 6:11 pm**

You could also just use the square bracket 'substring operators' to rearrange the date, then cat on the time and the zero seconds you'll need.

Posted: **Thu Nov 19, 2009 6:47 pm**

Ray and chulett,

Thanks much. Problem solved. Tried both ways.. BTW, I was not doing IConv right....

-Sam

DSXchange

Converting date to Timestamp !!

Converting date to Timestamp !!