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

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!

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 " />

No luck :thumbsdown:

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.

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

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

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)

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

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.