[SOLVED] An arithmetic expression with a DATETIME value is invalid while using PreparedStatement with Arithmetic calculation in sql later

An arithmetic expression with a DATETIME value is invalid while using PreparedStatement with Arithmetic calculation in sql later

I have the following sql query:

    SELECT COUNT(*) FROM PC_DATA
    WHERE PC_DATE BETWEEN ? AND (? + 4 MONTHS)
    AND UPPER(PC_TYPE) LIKE UPPER(?)

Now I trying to set the values of the Dates by the following code in JAVA:

Date start = new Date(quaterStart.getTimeInMillis());

stmt.setDate(1, start);
stmt.setDate(2, start);
stmt.setString(3, "%" + type + "%");

And when I run it I get the following exception: DB2 SQL Error: SQLCODE=-182, SQLSTATE=42816, SQLERRMC=null, DRIVER=4.16.53

While I try to run this sql query from a console in the following way, it runs without any issues:

 SELECT COUNT(*) FROM PC_DATA
  WHERE PC_DATE BETWEEN  DATE('2015-01-01') AND (DATE('2015-01-01') + 4 MONTHS)
  AND UPPER(PC_TYPE) LIKE UPPER('%laptop%');

What am I doing wrong? Thank you.

Solution

A datetime arithmetic expression is atomic, that is, <date> + 4 months is a complete expression, so you cannot substitute just one part of it with a variable (parameter). You will have to calculate the upper boundary date in your Java code and change the query text to ...WHERE PC_DATE BETWEEN ? AND ?.