MSSQL PreparedStatement for smalldatetime returns wrong result

Question

I have a table in my mssql database with one of the column as smalldatetime datatype.

smalldatetime datatype does not have seconds precision. As per its documentation:

ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute."

I have a table em_test with columns description(varchar) and startTime(smalldatetime).

I inserted following entries in table :
    insert into em_test values('e1-2018-08-01 23:59:59', '2018-08-01 23:59:59');
    insert into em_test values('e2-2018-08-02 00:00:00', '2018-08-02 00:00:00');
    insert into em_test values('e3-2018-08-02 01:00:00', '2018-08-02 01:00:00');
    insert into em_test values('e4-2018-08-02 23:59:59', '2018-08-02 23:59:59');
    insert into em_test values('e5-2018-08-03 00:00:00', '2018-08-03 00:00:00');

If I run query : select * from em_test where startTime between '2018-08-02 00:00:00' and '2018-08-02 11:59:59', below results are obtained:

name                    startTime
e1-2018-08-01 23:59:59  2018-08-02 00:00:00 (Rounded up)
e2-2018-08-02 00:00:00  2018-08-02 00:00:00
e3-2018-08-02 01:00:00  2018-08-02 01:00:00
e4-2018-08-02 23:59:59  2018-08-03 00:00:00 (Rounded up)
e5-2018-08-03 00:00:00  2018-08-03 00:00:00

The same query using prepared statement returns different result.

name                    startTime
e1-2018-08-01 23:59:59  2018-08-02 00:00:00 (Rounded up)
e2-2018-08-02 00:00:00  2018-08-02 00:00:00
e3-2018-08-02 01:00:00  2018-08-02 01:00:00

I debugged mssql-jdbc-6.4.0-jre7.jar source code to understand why the result returned by plain sql query and sql prepared statement is different. Below is the analysis:

1. Whenever prepared statement has to be executed, internally a runtime stored procedure is created wrapping the query. The query input params becomes Stored procedure input param.
2. While forming the prepared statement, we set smalldatetime value as timestamp as ps.setTimeStamp('2018-08-01 23:59:59').
3. While forming the runtime Stored proc, the input params datatype is determined. TimeStamp is mapped to mssql datetime2.
4. Hence in case of datetime2, the args '2018-08-02 00:00:00' and '2018-08-02 11:59:59' are not rounded off and it does not return the last two results.
5. Tried to simulate the runtime procedure also with few logs:

   create procedure smalldatetimetest @startDate datetime2, @endDate datetime2
   AS
       print 'Start Date in Datetime2 format : ' + Convert(varchar(50), @startDate);
       print 'End Date in Datetime2 format: ' + Convert(varchar(50), @endDate);
       print 'Start Date in smalldatetime format: ' + Convert(varchar(50), cast(@startDate as smalldatetime));
       print 'End Date in smalldatetime format: ' + Convert(varchar(50), cast(@endDate as smalldatetime));
       select * from em_test where datetime1 between @startDate and @endDate;
   GO 
   

Execution exec smalldatetimetest '2018-08-02 00:00:00','2018-08-02 23:59:59' prints below logs:

Start Date in Datetime2 format : 2018-08-02 00:00:00.0000000
End Date in Datetime2 format: 2018-08-02 23:59:59.0000000
Start Date in smalldatetime format: Aug  2 2018 12:00AM
End Date in smalldatetime format: Aug  3 2018 12:00AM

Is my understanding correct and Is there any workaround to fix the discrepancy in case of prepared statement?

Answer 1

The following workaround seems to be working for me:

Instead of supplying a java.sql.Timestamp parameter value

ps.setTimestamp(1, ts);

supply it as a string

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
ps.setString(1, sdf.format(ts));

That causes the JDBC-generated query to use an nvarchar(4000) parameter instead of a datetime2 parameter

exec sp_executesql N'SELECT COUNT(*) AS n FROM #tmp WHERE foo<=@P0        ',N'@P0 nvarchar(4000)',N'2018-08-11 02:59:59.001'

and the results appear to match those of a plain text query with a string literal date/time value, like the one in your question.