CAST() AND CONVERT() functions throwing .jdbc.SQLServerException: Incorrect syntax near ')'

Other SQL Server Topics
1

Hi I am able to run the below query in SQL Server 2012 without any issues

select pid 
from mydb.dbo.analysis  
where subject_key = ? and cast(my_date as date) = cast(? as date)  
order by my_date desc

But when this is executed through Java code, I am seeing below exception.

java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'

 Here is how my query is defined: 
        <entry key="scanid"> 
            <bean class="com.myorg.prod.model.Select"> 
                <property name="expression" value="pid" /> 
                <property name="from" value="mydb.dbo.analysis " /> 
                <property name="where" value="subject_key = ? and cast(my_date as date) = cast(? as date)  order by my_date desc " /> 
                <property name="params" value="subject_key,acq_date" /> 
            </bean> 
        </entry>

I see this problem when I am using CAST or CONVERT functions to convert and compare the dates.

Any help would be much appreciated!

2

Try adding single quotes for the date (and probably for the subject_key as well) as shown below:
<property name="where" value="subject_key = '?' and cast(my_date as date) = cast('?' as date) order by my_date desc " />

3

No luck :thumbsdown:

4

Can you get to a point where the actual SQL statement has been generated? Take that and see what syntax errors it has using SSMS.

5

Or perhaps "spy" on the actual SQL code of the statement sent to SQL using SQL Profiler

6

Above mentioned SQL is what it generates(I printed it in the logs and took it from there). And it works without any issues in SSMS

7

With the "?" ?

It may be the substitution of those "?" that is the issue (hence my suggestion to review the actual SQL sent to the server with SQL Profiler)

8

Ditto what Kristen said. The "above SQL" isn't SQL; its XML.

9

I think "the aove SQL" was meant to be this bit:

select pid 
from mydb.dbo.analysis  
where subject_key = ? and cast(my_date as date) = cast(? as date)  
order by my_date desc

but of course the "?" substitutions have not be done at that point and, I think, might well be responsible for "breaking" the syntax.