java.sql.SQLException: ORA-30081: invalid data type for datetime/interval arithmetic for the below mentioned query

Question

PreparedStatement ps1 = con.prepareStatement("update cart set address = ?,city =?,state=?,country=?,mobileNumber=?,orderDate=SYSDATE,deliveryDate= orderDate + INTERVAL '10' DAY ,paymentMethod = ?,transactionId = ?,status=? where email = ? and address is NULL");

orderDate & deliveryDate has varchar(100) datatype in db.

Why would your date columns have a varchar type? To get rid of the error, you will need to convert the date into a string. — Garet Jax
– Garet Jax, Commented Feb 1, 2022 at 19:15

Optimizer · Accepted Answer · 2022-01-23 05:56:45Z

0

The datatype of the returned value for SYSDATE is DATE. As orderDate & deliveryDate is varchar(100) datatype, you need to use TO_CHAR() to convert it. use something like below

update cart set address = ?,city =?,state=?,country=?,mobileNumber=?,
orderDate=TO_CHAR(SYSDATE, 'yyyy/mm/dd'),
deliveryDate= TO_CHAR(SYSDATE + INTERVAL '10' DAY,'yyyy/mm/dd') , 
paymentMethod = ?,transactionId = ?,status=? where email = ? and address is 
NULL");

answered Jan 23, 2022 at 5:56

Optimizer

2711 gold badge2 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

java.sql.SQLException: ORA-30081: invalid data type for datetime/interval arithmetic for the below mentioned query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related