Update statement works in Oracle 9i but not in Oracle 11G

Question

I'm facing an issue with an update statement that worked earlier in Oracle 9i but now it does not update any rows in Oracle 11G. Here is the statement that i'm using.

update account  
set 
  days_to_validate = validated_date - val_requested_Date 
where 
  validated_date >= val_requested_date

The validated_date and val_requested_date are both date fields in the format: dd-mmm-yyyy (18-Mar-2015). This was working earlier in Oracle 9i before we did an upgrade.

Pls advice on how we can fix this.

Thanks Prashanth

are they date fields? Or are they varchar fields storing dates? — yu_sha
– yu_sha, Commented Apr 8, 2015 at 11:56
Please provide definition of account table, e.g. create table statement. — ThinkJet
– ThinkJet, Commented Apr 8, 2015 at 12:07
Hello Guys, I was able to fix this. I tried including the "to_date" function and it worked in Oracle 11G. Here is the change i made to the query. — Prashanth Sivaraman
– Prashanth Sivaraman, Commented Apr 8, 2015 at 12:43
Missed the query in my earlier comment: update account set days_to_validate=to_date(validated_date)-to_date(val_requested_Date) where to_Date(validated_date)>= to_date(val_requested_date) — Prashanth Sivaraman
– Prashanth Sivaraman, Commented Apr 8, 2015 at 12:44

David Faber · Accepted Answer · 2015-04-08 19:40:15Z

1

I was able to fix this. I tried including the "to_date" function and it worked in Oracle 11G. Here is the change i made to the query.

update account
    set days_to_validate = to_date(validated_date) - to_date(val_requested_Date)
  where to_Date(validated_date) >= to_date(val_requested_date)

edited Apr 8, 2015 at 19:40

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

answered Apr 8, 2015 at 12:51

Prashanth Sivaraman

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

David Faber Over a year ago

To be completely safe you should give the format in the TO_DATE() function as well - e.g., TO_DATE(validated_date, 'dd-mmm-yyyy')

Collectives™ on Stack Overflow

Update statement works in Oracle 9i but not in Oracle 11G

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related