DATEDIFF not working in JAVA

Question

Am running a query from JAVA , the query contains DATEDIFF function in which the parameter is a keyword,we can give yy,mm,day,etc.. so am getting these keyword from user as parameter and setting it in query, but the issue is it wont execute, it gives an exception as Invalid parameter for argument 1. If i run the same query in CLIENT it gives result, but if i give quotes to keyword like 'day' it gives the same error in CLIENT also. So my question how will I set it in the query from JAVA. Currently am doing like

for (String param : params) {
 try {
        namedParameterStatement.setObject(param,requiredFilterValues.get(param));
     } catch (RuntimeException e) {
       }
}

This is the query am using

SELECT CUST_KEY,Eff_Date_From,Eff_Date_To,  DATEDIFF(:intervals,Eff_Date_From,Eff_Date_To) as datediffs,Active  FROM CUSTOMER_DIM   WHERE Active = :active

Am passing values for params intervalsand active, The issue is with :intervals as it takes keywords.

I think the java program setting the parameter as string that is why its generating error.. How can i implement this?

You need to provide more code: at minimum the query and parameter values used, as it stands we can't possibly answer your question with the code provided. For all we know, you are trying to assign the function as the parameter **value**. — Mark Rotteveel, Feb 04 '16 at 19:04
@MarkRotteveel no am not passing function as parameter value, i clearly specified my query contains DATEDIFF function which takes various key words , that keywords am passing as parameter and setting in my query — Anoop LL, Feb 04 '16 at 19:13

score 0 · Answer 1 · answered Feb 04 '16 at 19:37

The problem is that you can only pass values using parametrized queries. You can't pass keywords (nor object names). In the case of your query. they would be passed as the equivalent of DATEDIFF('<your-keyword>', .., which wouldn't work either.

There is no solution except manually concatenating the required keyword into the query, or creating a convoluted query using a CASE-construct to get the right value depending on the value passed:

case ? when 'MONTH' then datediffer(MONTH, ..) when .. end

You may need to explicitly cast the parameter to a VARCHAR to get it to work.

DATEDIFF not working in JAVA

1 Answers1