OCI Date Conversion failed

[Amit_111]

Hi All,

i have a source table having some date fields. i am extracting the data for these columsn in the 'Oracle OCI 9' Stage. But when I import the metadata for the same table through DataStage; the Datatype is coming as "TimeStamp 38". Why is DS importing the column datatype as "Timestamp 38" even when the format is "date" in the actual database.

I changed this "Timestamp 38" to "date" in the Soure 'Oracle Stage--> columns' tab but when i do "View Data" to see the output it is giving me error as "OCI Date Conversion failed". I am not able to get why this is happening when actually the data is in "Date" format in the source and also i want to view it as "date" format in OCI stage but still it is giving me error.

I searched for this error in the forum and found that this seems to be some DataStage error and the DataStage Support team needs to contacted for this.
But just wanted to confirm once before going for support.
Thanks in advance

[chulett]

DATE fields in Oracle are timestamps. I use them with a datatype of Timestamp without issue and actually prefer to always include the time portion of the DATE field even when it "doesn't have one".

Did you actually try it? What issues did you have?

[Amit_111]

Actually, i kept them as it is initially and did the "View Data" but then i got the error saying "Attempt to convert string value '01-SEP-01' to Timestamp type unsuccessful".
But i checked the source datatype in table table and it is actually a date format.

Still i then changed the timestamp to varchar and then i was able to see the data through 'View Data'. Again i changed the Varchar to date and tried "View Data" which gave me the error "OCI Date Conversion failed". Dont know why this is happening even though it is a date format in the Source table.

[csri]

Hi All,

i have a source table having some date fields. i am extracting the data for these columsn in the 'Oracle OCI 9' Stage. But when I import the metadata for the same table through DataStage; the Datatype is coming as "TimeStamp 38". Why is DS importing the column datatype as "Timestamp 38" even when the format is "date" in the actual database.

I changed this "Timestamp 38" to "date" in the Soure 'Oracle Stage--> columns' tab but when i do "View Data" to see the output it is giving me error as "OCI Date Conversion failed". I am not able to get why this is happening when actually the data is in "Date" format in the source and also i want to view it as "date" format in OCI stage but still it is giving me error.

I searched for this error in the forum and found that this seems to be some DataStage error and the DataStage Support team needs to contacted for this.
But just wanted to confirm once before going for support.
Thanks in advance

You are extracting the data with date fields from the source table... what is the query that you are using? Are you converting the Oracle date to timestamp format? If you want to see only date portion of the Oracle date field then you have to do to_char(datefield,'YYYY-MM-DD'). Please more details on the source stage.

[chulett]

Actually, i kept them as it is initially and did the "View Data" but then i got the error saying "Attempt to convert string value '01-SEP-01' to Timestamp type unsuccessful".
But i checked the source datatype in table table and it is actually a date format.

Datatype is only one piece of the puzzle. Your SQL is the other. Generated SQL will always wrap a TO_CHAR() around a DATE field - I'm guessing you are using custom sql, yes?