Lookup Error ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Question

When I try to run this query I get the above error. Could some one help me on this

UPDATE CCO.ORDER_CREATION 
   SET "Doc_Date" = 8/9/2013 
 WHERE "Document_Number" IN (3032310739,3032310740,3032310738)

Answer 1

8/9/2013 is a numeric value: 8 divided by 9 divided by 2013.

You should use the to_date() function in order to convert a string to a date:

UPDATE CCO.ORDER_CREATION 
   SET "Doc_Date" = to_date('08/09/2013', 'dd/mm/yyyy')
 WHERE "Document_Number" IN (3032310739,3032310740,3032310738);

You might need to adjust the format mask, as it's unclear whether you mean August, 9th or September 8th

Alternatively you can use the an ANSI date literal (the format is always yyyy-mm-dd for an ANSI SQL date literal):

UPDATE CCO.ORDER_CREATION 
   SET "Doc_Date" = DATE '2013-09-08'
 WHERE "Document_Number" IN (3032310739,3032310740,3032310738);

Answer 2

Try this

UPDATE CCO.ORDER_CREATION 
   SET "Doc_Date" =   TO_DATE('8/9/2013', 'MM/DD/YYYY')
 WHERE "Document_Number" IN (3032310739,3032310740,3032310738)