Date Conversion Problem

[NatPV]

Hi all,
I'm at the beginning. I have to extract date from a file (in char format) and I have to store it in Oracle DB. The type of the destination field is timestamp 19. Well, the only row loaded is the first and the value for the timestamp is 0000-00-00 00:00:00. Then, whem I look into the Oracle destination table, I found the value 00-000-00.
How can I resolve this incompatibility format problem?
Thank you in advance for your attention.

Bye, NatPV.

 

[Solukas]

Without more detail I cannot say for sure what the exact issue is, but here are a couple things to try.

Try setting the sequential file field to date rather than char on the input stage. This may help DataStage internals process it correctly because it will know it is a date.

The format Oracle is returning, 00-000-00, appears to be the standard date mask format (DD-MMM-YY) and can be altered to look any way you wish using nls_date_format. With this in mind, you might try to format the date within DataStage, using DataStage date functions, to put it in the correct format before it hits the database.

Hope one of these work for you.
Sol

 

[NatPV]

Hi Solukas,
thank you for your answer. I have to load a time table (dimensional) and the fields have timestamp format. The table is stored in Oracle DB. I have used the ORAOCI stage and I did not have problems. When then I used the ODBC stage I wasn't able to load the table again.
I used some date functions to put the date estracted from the source text file to put this date in this format: YYYY-MM-DD hh24:mi:ss that I believe is the native format date for Oracle. I have proved the validity of my transformation function writing the result on a text file, so that I have verified that data was in the format I wanted.
My thought was that I could then use to_char SQL function when viewing data loaded into the table.
I hope to have been clear. Thank you in advance for your suggestions.
Nat.