Datetime2 rounding issue

We are working on a multi-database application (read and write to multiple databases). The datamodel is identical on the databases.

We are inserting a timestamp (12 fraction digits) value in a datetime2(6) column in MSSQL however MSSQL rounds the value making it different to other databases where the extra fraction digits are ignored.

Example:

DECLARE @t TABLE(x DATETIME2(6)) 
INSERT @t SELECT '2017-03-28 14:00:59.4106489'
SELECT x FROM @t

Result: 2017-03-28 14:00:59.410649
Expected: 2017-03-28 14:00:59.410648

DB2 provides the expected result by throwing away the 7th fraction digit.

How can we make MSSQL not round the datetime2 value ?

EDIT

The application writes a java.sql.Timestamp object with 12 fraction digits to DB2 and MSSQL. In DB2 the column is a TIMESTAMP(6) and in MSSQL a DATETIME2(6). DB2 truncates from 12 fraction digits down to 6. MSSQL rounds down to 6.

I don't think there is a way to make SQL Server default to truncating incoming values instead of rounding.

You can however control the values in your Java application, before they are sent to the databases.

According to Java 8 docs, java.sql.Timestamp has getNanos() and setNanos() methods you can use (pseudocode, my Java is rusty):

ts = ... // the Timestamp object

micros = ts.getNanos() / 1000 ; // extract and truncate to microseconds
ts.setNanos( micros * 1000 ) ; // set the truncated value back

insert(..., ts, ...) ; // INSERT


StackExchange Database Administrators Q#169175, answer score: 2