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