CODEWITHSUNDEEP
sqloracle-database
santhosha
santhosha

Reputation: 629

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

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)

Upvotes: 8

Views: 147089

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

Try this

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

Upvotes: 9

fazlook1
fazlook1

Reputation: 139

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)

Upvotes: 0

Harshit
Harshit

Reputation: 560

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)

Upvotes: 1

user330315
user330315

Reputation:

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);

Upvotes: 9

Related Questions