Date conversion problem

[hhh]

Hello DS Friends,

In my job , source and target are oracle tables .source having portfoliodate column and its datatype is TIMESTAMP. for ex its value is 1998-01-31 00:00:00 . while conveting portfoliodate(Timestamp) into target date(dd-mon-yy) format I am using following Iconv,Oconv function.
1)Substrings(DSLink4.PORTFOLIODATE,1,10)
2)Oconv(Iconv(aa, "D-YMD"), "D-DMY[,A3,2]") .
While running job , i am getting following warnig in every portfoliodate values : "Graph_CSV_sf_Test..Transformer_1: At row 2333, link "DSLink5", while processing column "PortfolioDate"
Value treated as NULL
Attempt to convert String value "31-Jan-98" to Date type unsuccessful"

why value treats as Null that i am not getting, Please share your ideas on this issue.

Thanks
HHH

[ArndW]

Are you sure that your default date format is being used? Execute a SQL "SELECT sysdate from dual;" using your favorite tool and see if that format matches yours.

[hhh]

Yes,default format of date in oracle is dd-mon-yy only, i hv checked it from dual.
However here , portfoliodate has been converted to default format only.

Are you sure that your default date format is being used? Execute a SQL "SELECT sysdate from dual;" using your favorite tool and see if that format matches yours. ...

[kumar_s]

Are you sure your target formated to dd-mon-yy and not as dd-mm-yyyy?

[hhh]

default oracle's date format is dd-mon-yy only,so i have converted it according to target format only.

Are you sure your target formated to dd-mon-yy and not as dd-mm-yyyy?

[ArndW]

Which stage are you using to write to Oracle?

[sb_akarmarkar]

Why dont you try to insert manually 1 row in target with this date format.... May be one of the way to check format of table...

Thanks,
Anupam

[hhh]

Manually also i entered the record with this format, and it works fine with this date format.

Why dont you try to insert manually 1 row in target with this date format.... May be one of the way to check format of table...

Thanks,
Anupam

[sb_akarmarkar]

Is it happing to that 2333 row only ? with timestamp value "1998-01-31 00:00:00"

Thanks,
Anupam

[m_keerthi2005]

if you use data type as Date or Timestamp, it does not work, because it is not able to treat it data type as Date. if you can use Varchar data type this will work. if you have not any problem you can use. this could be one of the solution.

[chulett]

In my job , source and target are oracle tables .source having portfoliodate column and its datatype is TIMESTAMP. for ex its value is 1998-01-31 00:00:00 . while conveting portfoliodate(Timestamp) into target date(dd-mon-yy) format I am using following Iconv,Oconv function.

Your 'default' or NLS_DATE format don't come into play if you are treating these fields as dates. If you are using OCI stages with Generated SQL and a Timestamp datatype, (which you should be, in my opinion) then your incoming value is already in the proper format for the target.

Drop your substring/Conv transformations and just push the portfoliodate over untouched.

[raji]

Hi all,

As i am new to Datastage, I want to know that the Functions "Iconv and Oconv" works in Parallel Extender jobs or not.

If not which is the equivalent function to this in parallel jobs.

Please ppl help me out.

thanks in advance

[balajisr]

IConv and OConv are not available in PX.

This post should be in parallel forum and not in server forum.

[sateeshbabu]

Hi Balajisr,

Could you let me know parallel forum link.

[balajisr]

Hi Balajisr,

Could you let me know parallel forum link.

Click on the Forum link on the top of the page. You will get list of forums. Parallel forum is the next link to Server Forum. Parallel forum is same as Enterprise edition forum.

viewforum.php?f=8