Datetime2 rounding issue

Question

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.

score 4 · Answer 1 · answered Apr 14 '23 at 16:40

You can't make SQL Server default to truncating, but you can calculate it by subtracting off the unwanted fractional seconds using dateadd and datepart. We'll use nanoseconds mod 1000 to get the fractional seconds to keep for datetime2(6). For example:

declare @t table(x datetime2(7)) 
insert @t select '2017-03-28 14:00:59.4106489'
select x, truncated = convert(datetime2(6), dateadd(nanosecond, -datepart(nanosecond, x) % 1000, x)) from @t

x	truncated
2017-03-28 14:00:59.4106489	2017-03-28 14:00:59.410648

This method is more efficient than converting the value to text, truncating the text, and then converting the text back to a datetime2.

Here's another related example, this time targeting datetime2(2) to show the difference between a standard convert and this truncating convert for several inputs. Notice that this uses nanoseconds mod 10000000 to get the fractional seconds to keep for datetime2(2).

declare @test table (D7 datetime2(7));
insert into @test values 
('2023-04-14 08:38:59.9999999'),
('2023-04-14 08:38:59.999999'),
('2023-04-14 08:38:59.99999'),
('2023-04-14 08:38:59.9999'),
('2023-04-14 08:38:59.999'),
('2023-04-14 08:38:59.99'),
('2023-04-14 08:38:59.9'),
('2023-04-14 08:38:59');
select 
    D7, 
    ConvertD2 = convert(datetime2(2), D7),
    TruncateD2 = convert(datetime2(2), dateadd(nanosecond, -datepart(nanosecond, D7) % 10000000, D7))
from @test
order by D7 desc

D7	ConvertD2	TruncateD2
2023-04-14 08:38:59.9999999	2023-04-14 08:39:00.00	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9999990	2023-04-14 08:39:00.00	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9999900	2023-04-14 08:39:00.00	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9999000	2023-04-14 08:39:00.00	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9990000	2023-04-14 08:39:00.00	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9900000	2023-04-14 08:38:59.99	2023-04-14 08:38:59.99
2023-04-14 08:38:59.9000000	2023-04-14 08:38:59.90	2023-04-14 08:38:59.90
2023-04-14 08:38:59.0000000	2023-04-14 08:38:59.00	2023-04-14 08:38:59.00

score 2 · Answer 2 · answered Apr 05 '17 at 18:29

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

score 1 · Answer 3 · answered Apr 06 '17 at 20:45

you can achieve this using a simple casting method

DECLARE @dt AS Datetime2
SET @dt = CAST('2017-03-28 14:00:59.4106489' AS DATETIME2)
DECLARE @t TABLE(x DATETIME2(6)) 
INSERT @t SELECT CAST(LEFT(@dt,LEN(@dt) - 1) AS DATETIME2(6))
SELECT x FROM @t

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

you cannot achieve this without trimming the last digit

Datetime2 rounding issue

3 Answers3