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

Try this

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

Answer 2

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 3

At present you are passing date as a number string, convert it in date and then try to insert as below

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

Answer 4

This is how i see this:

UPDATE CCO.ORDER_CREATION tbl1
   SET tbl.Doc_Date =   TO_DATE('08/09/2013', 'MM/DD/YYYY')
 WHERE tbl1.Document_Number IN (3032310739,3032310740,3032310738)