Converting String to Date Format

[mirogak]

Hi,

Using:
PowerCenter 7.1.5
Database : Oracle 8i

Source table has a column (SOURCE_COL) with string data type but has date values in it, in this format '09-MAR-2007'

Target table has a column (TARGET_COL) with date data type. The date format in this database is 'MM/DD/YYYY'

So, I use an expression transformation to convert to date format using the TO_DATE function. However, this does not work.

In the expression transformation I created 3 ports:
IN_SOURCE_COL (string type)
VAR_SOURCE_COL (date type)
- here, I use the formula "TO_DATE(IN_SOURCE_COL,'MM/DD/YYYY')"
OUT_SOURCE_COL (date type)
- here, I simply call VAR_SOURCE_COL

I then connect the OUT_SOURCE_COL to the TARGET_COL. I get a valid expression and mapping. And the workflow executes fine, however, it does not actually write anything to that target column. I get the following error message in the log file:

" Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date "

So, it seems that PowerCenter can't convert this string '09-MAR-2007' to this date format '3/9/2007'

Can anyone help here?

Thanks,
Mirogak

 

[ArtieChoke]

I believe you need to tell it what format the string date is in, not what you want it to return. It is going to return a date type and format has nothing to with that at the target.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[blom0344]

I recall formatting the date (from source) to the correct string representation and then using to_date function to get the proper date format. So, a 2-step exercise always worked for me..

Ties Blom

 

[rasETL]

Here is some code which is coverting a 26 character string to datetime...

TO_DATE(SUBSTR(in_DATE1, 0, 19),'YYYY-MM-DD HH24:MI:SS')

Hope that helps.