We have recently moved our dev database from SQL Server 2005 to SQL Server 2008 R2, and we have encounter the following situation.
If we insert a record with a DATETIME column and we then try to use this inserted time in a where clause, the query will sometimes return no results, this is because the rounding that occurs with the DATETIME data type.
Here's an example of how to replicate this behavior:
import java.sql.*;
public class JDBCTest {
public static void main( String args[] )
{
try
{
// Load the database driver
Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" ) ;
// Get a connection to the database
Connection conn = DriverManager.getConnection("jdbc:sqlserver://DEVENV;user=?;" +
"password=?;databaseName=jdbctest" ) ;
//Insert
PreparedStatement ps = conn.prepareStatement( "INSERT INTO dbo.test VALUES (?)" ) ;
java.util.Date date= new java.util.Date();
java.sql.Timestamp currentTimestamp= new java.sql.Timestamp(date.getTime());
ps.setTimestamp(1, currentTimestamp );
ps.execute();
ps.close() ;
PreparedStatement psSel = conn.prepareStatement( "SELECT * FROM test WHERE date = ?" ) ;
psSel.setTimestamp(1,currentTimestamp);
ResultSet rs = psSel.executeQuery() ;
if (rs.next()){
while( rs.next() ) System.out.println( rs.getString("id") + " " + rs.getString("date") ) ;
}
else {
System.out.println("NO RESULTS!");
}
rs.close() ;
psSel.close() ;
conn.close() ;
}
catch( Exception e )
{
System.out.println( e ) ;
}
}
}
Using the SQL Profiler we saw that the following is executed:
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 datetime2',N'INSERT INTO dbo.test VALUES (@P0) ','2014-04-24 09:49:41.3360000'
select @p1
And in the database this is the record that was inserted:
2014-04-24 09:49:41.337
When the where is executed the following is sent to the database:
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,N'@P0 datetime2',N'SELECT * FROM test WHERE date = @P0 ','2014-04-24 09:49:41.3360000'
select @p1
The SELECT returns no data. The problem is that for some reason the JDBC driver sends the parameter as DATETIME2, and the engine then compares the DATETIME and DATETIME2 without taking into account DATETIME's rounding, so the where clause fails.
With SQL Server 2005 the behavior is the expected one as DATETIME2 was introduced in the 2008 version.
So far the solutions we have come up with are, implicitly casting the parameters to DATETIME. And in the long run change the database DATETIME columns to DATETIME2, but in the mean time this last solution is not feasible.
Are there any workarounds to this problem?