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.
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 ?
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.