SQL Server 2005 to 2012 com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed

Question

We are migrating from SQL Server 2005 to 2012. One of the functionality is failing with this error

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

We use SQLJDBC driver 3.0 to communicate with SQL Server 2012. I see this issue is happening when we try to insert NULL value into a DATETIME column (nullable). Same however works in 2005. Any help with this issue is appreciated.

It's a simple INSERT statement that fails from Java to SQL server 2012 using MyBatis ORM:

Insert into temp_test (date1, name, date2) values ('2010-10-10 00:00:00.0','test',null) 

This insert fails when we try from our app (using SQL JDBC driver)... However same code base works without any issue in SQL 2005.

Answer 1

I just ran into this error with MyBatis and SQL Server 2012 when attempting to do an insert where a DATETIME2 column could be null. The solution to my issue was specifying the JDBC type in my mapper file as specified in the MyBatis documentation.

From: http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Parameters

The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.

On the insert statement in the mapper file, the value for the date2 column would have "jdbctype = TIMESTAMP" added to it, like so:

<insert id="testInsert" parameterType="com.example.object">
    INSERT INTO tempt_test
      (date1, name, date2)
    VALUES
      (#{date1}, #{name}, #{date2, jdbcType = TIMESTAMP})
</insert>

Answer 2

I can recommend the following read on migrating from to SQL Server 2012, written by Thomas LaRock (SolarWinds).

Relevant quotes from the article:

• Using the SQL 2012 Upgrade Advisor
• Review the Breaking Changes
• Reviewing the Behavioral Changes
• Executing DBCC CHECKDB WITH DATA_PURITY (will check your data for values that are no longer valid)

Further, it is hard to say more specific things about your issue without seeing the table definition. I would however suggest the following things:

• DATE/TIME strings should be formatted in ISO 8601 format as much as possible: eg '2010-10-10T00:00:00.0'
• If you still experience issues, try inserting with CAST('2010-10-10T00:00:00.0' AS DATETIME) or CONVERT(DATETIME,datetimecolumn,<format>)
• Reset the compatibility level of your database to the appropriate version for SQL Server 2012: ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL=110;
• Upgrade your SQLJDBC driver to the latest version (current version is 6.0)
• Use a different driver eg JTDS JDBC Driver (open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server, 2012 supported)
• Install the latest Service Pack for SQL Server 2012 (latest is Service Pack 3; overview).
• Review MyBatis for any incompatibilities with SQL Server 2012.