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.
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 ?
.
See also this somewhat related question.