Oracle stage gives wrong dates

[vintipa]

Hi experts,

i am reading a date column from an oracle table and writing it to a file.
its a very simple select query[select distinct doc_date as gdatu from VBAK_AR] i am using in the Oracle OCI stage.

But when i read the dates using Oconv function [Oconv(DSLink25.GDATU,"D/DMY[2,2,4]")] i get wrong dates which are not in the table. specifically i get dates greater than actual dates.

can u guess what can be wrong???

regards,

[chulett]

There shouldn't be any need to use 'OConv' on dates coming from a select, they should be usable directly. What does the date look like before you OConv it? What data type are you using for it?

[vintipa]

Hi,

My job design is simple. OracleOCI --> Transformer -->Seq File.

when i do right click on OracleOCI stage and view data, then it shows dates in internal format. when i view the dates in TOAD then maximum date that i get is 12-02-2009. So i put Oconv in transformer and loaded the dates to the file. then when i see the dates int the file it shows me the date of 2012, 2016, etc.

regards,

[ray.wurlod]

Can you please show us? I want to see what "internal format" means to you. Can you also show us the SQL statement being executed by the Oracle stage?

[chulett]

You get those integer values when using Date as the data type, change it to Varchar and use TO_CHAR() with the appropriate mask in the select statement. I don't know what they are, but they're not DataStage internal values so OConv is not the right thing to use with them.

[vintipa]

Hi,

A sample of the output of the table view in datastage is as follows:

GDATU
---------
12152
12517
12882
13248

the query is mentioned above in the post. this is the internal format i am getting.

[vintipa]

Hi Friends,

The issue is resolved now. i used to_char function to read the out put of the query and column datatype as varchar. this gave me the correct dates.

thanks experts. Your suggestions helped me.

regards,