Error "The data types time and datetime are incompatible in the equal to operator" using MSSQL "time" data type

Question

I need to query/insert/update records in an MSSQL database that uses the "time" data type with a Parameterized Query.  I can not change the database schema.  When I use setTime() I get this error:

com.qvera.qie.exception.NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: The data types time and datetime are incompatible in the equal to operator.

If I try to use setDate() i get this error:

com.qvera.qie.exception.NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: The data types time and date are incompatible in the equal to operator.

Answer 1

A couple options:

o  Use setString() with the string representation of time.  

pq.setString("timeParam", new java.sql.Time(System.currentTimeMillis).toString());

or

pq.setString("timeParam", "08:05:25");

o  Use the sendTimeAsDateTime=true on the connection string.  Example:

jdbc:sqlserver://localhost:1433;databaseName=thedatabasename;sendTimeAsDateTime=false