ERROR ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Question

I am trying to execute the following sql statement for an oracle database:

UPDATE PARENT
SET RENEW_DATE = TO_DATE('08/31/' + EXTRACT(YEAR FROM JOINED), 'MM/dd/yyyy')
WHERE STATUS_IND = 'Active';

I am expecting to get the year piece from the field called Joined which is a date formatted like MM/dd/yyyy. I want the end result, or the value I am setting to look like '8/31/2015' for example.

Any help is appreciated I have tried multiple things.

I apologize, what I was getting before is the ORA-00932, with the above I am getting ORA-01722: invalid number — user3845574

Chris Barlow Chris Barlow · Accepted Answer · 2014-09-15T19:54:55

Use a || instead of the + sign to concatinate the date together.

UPDATE PARENT 
  SET RENEW_DATE = TO_DATE('08/31/' || EXTRACT(YEAR FROM JOINED), 'MM/dd/yyyy') 
WHERE STATUS_IND = 'Active'

ERROR ORA-00932: inconsistent datatypes: expected DATE got NUMBER

1 Answers