updating date field

[dnat]

Hi,

I have a field Col1 with data type Date.
From the oracle stage i am extracting this field like to_char(col1,'YYYY-MM-DD') and storing this value in the col.

I have one more field Col2with data type Timestamp.
From oracle stage i am extracting this field like to_char(col2,'YYYY-MM-DD HH24:MI:SS') and storing it in Col2.

Now the target field is Col 3 with data type Date.

Based on some condition i am storing either col1 or Col2. But when i try to store Col2(which is timestamp) in col 3 , it is giving error

CvBT0002Job3AcctHistLoad_New.3267.XF1: At row 1, link "ToAcctHist", while processing column "AH_PREV_ACCT_RECLASS_DATE"
Value treated as NULL
Attempt to convert String value "2009-05-01" to Date type unsuccessful

[dnat]

ok, i saw that this is present in the update statement

AH_PREV_ACCT_RECLASS_DATE=TO_DATE(:93, 'DD-MON-YY').

how do i convert my date into this format

[priyadarshikunal]

Passing string in 'YYYY-MM-DD' and trying to use the format 'DD-MON-YY'.
How can you expect it to work.

[dnat]

Even after converting it into 'DD-MON-YY' , i am not able to update it.

I tried this

Oconv(Iconv(Substrings(LkpMatched.AH_ACCT_RECLASS_DATE,1,10),"DYMD"),"D-DMY[2,A3,2]")

The format of AH_ACCT_RECLASS_DATE is 'YYYY-MM-DD HH24:MI:SS'

CvBT0002Job3AcctHistLoad_New.3273.XF1: At row 1, link "ToAcctHist", while processing column "AH_PREV_ACCT_RECLASS_DATE"
Value treated as NULL
Attempt to convert String value "01-MAY-09" to Date type unsuccessful

[Sainath.Srinivasan]

Take one step at a time. Add one function at a time and see where it throws an error.

It seems the data coming into the tx is of dd-mon-yyyy format.

[chulett]

You could always "fix" the update SQL so the format mask matches what you are already doing. There's no reason on God's Green Earth to force it into that particular format.