2

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
Viggy
  • 65
  • 2
  • 7
  • Please post an [MVCE](https://stackoverflow.com/help/mcve) if possible, or at least provide us with as much code and information as possible. – TT. Jan 14 '16 at 20:07
  • Added additional details to initial thread... – Viggy Jan 18 '16 at 22:03
  • Explain please why you refer to `DATETIME2` in your question, but you state that the column is of type `DATETIME`. Also `DATETIME2` was introduced in SQL Server 2008... yet you state that you are migrating from 2005 to 2012? That doesn't add up. Please clarify. – TT. Jan 19 '16 at 08:35
  • That error message took me off guard as well.. The column in the table is DATETIME but when we send a null value to DB an implicit conversion of varbinary to datetime2 is happening in SQLServer 2012 paradigm and hence we see this discrepancy error message. – Viggy Jan 26 '16 at 15:10
  • Did you try the suggestions I gave in my answer? – TT. Jan 26 '16 at 15:12

2 Answers2

5

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>
Jeremy V
  • 168
  • 1
  • 7
2

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

Relevant quotes from the article:

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.
TT.
  • 15,774
  • 6
  • 47
  • 88
  • Thanks for the detailed comment. My sincere apologies, it took a little while than expected to reply to this issue. The issue is resolved now. Its the problem with SQL JDBC driver that we use and the way SQL 2012 interprets null values for a datetime time. Insert query from Java should have an explicit convert function to forcefully insert it as datetime to avoid this unexpected error.. This is not been a case with 2005 or 2008 versions. Ex : convert(DATETIME,startdate,21), – Viggy Jan 26 '16 at 15:17
  • @Viggy Ah, the second suggestion I gave =). I've had to do this once myself. – TT. Jan 26 '16 at 15:19
  • @Viggy If you think my answer has value for future visitors experiencing the same problem, please accept or upvote the answer. GL! – TT. Jan 26 '16 at 15:20
  • Just an FYI... Cast did not work either but it has to covert function. cast threw same exception stack. – Viggy Jan 26 '16 at 15:22
  • @Viggy IC, I'll update that into the answer for future reference. – TT. Jan 26 '16 at 15:23
  • @Viggy What did the CONVERT statement look like? – TT. Jan 26 '16 at 15:24
  • @Viggy Thank you for the edit. I've highlighted it to stand out a bit more. Hope this can help a future visitor! – TT. Jan 26 '16 at 15:27