Oracle Date field to Timestamp

[reddy]

Hi Gurus,

I have both source and target tables as Oracle database.I am using custom sql in the source table to load into target.
I have a situation like this:

for postdate field source datatype is numeric but target datatype is timestamp
so in userdefined SQL i used
to_date(J1.JC_COST_DATE,'YYYYMMDD')

for currentfield is date datatype in source table but timestamp in target table

while running the datastage job i am getting following error:
Attempt to convert String value "15-MAR-06" to Timestamp type unsuccessful

Can you please light on me.

Thanks in advance.

Reddy

[lstsaur]

Hi Reddy,
Oracle does not have a timestamp datatype. Therefore, timestamp fields intended for an Oracle target will need to be converted a to a date datatype.

[kris007]

Try using TO_CHAR in place of to_date.

Kris

[rasi]

Reddy

Your format is not correct that's the reason you are getting this error.

Attempt to convert String value "15-MAR-06" to Timestamp type unsuccessful

Use to_date(J1.JC_COST_DATE,'DD-MON-YY') that should work

[kcbland]

All source data columns of data type DATE should use a TO_CHAR function with a mask of YYYY-MM-DD HH24:MI:SS. DS automatically puts this on the SQL for auto-generated SQL, but since you hand-wrote your SQL you must do this.

All target columns of data type DATE or TIMESTAMP should use a TO_DATE function with a mask appropriate to your NLS setting, usually YYYY-MM-DD HH24:MI:SS. Again, DS automatically puts this on the SQL for auto-generated SQL, but if you hand-wrote your DML you must do this.

[lstsaur]

Hi Reddy,
Sorry, I misunderstood your problem. Try TO_CHAR(TO_DATE(J1.JC_COST_DATE, 'DD-MON-YY'), 'YYYY-MM-DD HH24:MI:SS') and make sure your target column's datatype is defined as VARCHAR2(32).
Please let me know the result.