Sidebar

Error: "Operand type clash: datetime2 is incompatible with timestamp" using MSSQL "timestamp" datatype

0 votes
4.8K views
asked Nov 11, 2016 by rich-c-2789 (15,250 points)
I have a MSSQL table with a TIMESTAMP column.  When I try to query it using a ParameterizedQuery and setTimestamp() i get this error:

com.qvera.qie.exception.NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: datetime2 is incompatible with timestamp

How can I resolve this?

1 Answer

0 votes

MSSQL data type DATETIME2 (DATETIME in older versions) map best to a java.sql.Timestamp.

The MSSQL TIMESTAMP data type does not represent a date and should not be used with setTimestamp() or with java.sql.Timestamp.  To store dates in MSSQL use the TIME, DATE, or DATETIME2 (replaces DATETIME in newer versions) data types.

See this link regarding the Microsoft Sql Server TIMESTAMP data type (replaced/renamed with/by ROWVERSION in later versions): 

https://msdn.microsoft.com/en-us/library/ms182776.aspx

A couple things to point out from the link above.

First, in the definition it includes: 

"The rowversion data type is just an incrementing number and does not preserve a date or a time."

Next it has this note:

System_CAPS_ICON_note.jpg Note

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

A note regarding MySQL:  MySQL has a TIMESTAMP data type that does store a date time.  MySql TIMESTAMP stores all dates in the database as UTC.  It will convert all dates between the servers time zone and UTC so be cautious about changing the servers timezone.  Also be aware that it can be configured to behave in a magical way (automatically update on inserts and updates to any field in the record).  https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html 

answered Nov 11, 2016 by rich-c-2789 (15,250 points)
edited Nov 17, 2016 by rich-c-2789
...