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.


INSERT @t SELECT '2017-03-28 14:00:59.4106489'

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.

  • If your source is a string you can truncate it manually using substring('2017-03-28 14:00:59.4106489',1,26). Btw, imho both results are wrong, Standard SQL doesn't allow truncating a timestamp, but when you request something like CURRENT_TIMESTAMP(2) it's truncated, not rounded :-)
    – dnoeth
    Commented Apr 5, 2017 at 10:18
  • 2
    Is there a reason you aren't explicitly sending the data to SQL Server in the format (including truncation & precision) in the exact way you want it stored? Relying on implicit conversion/truncation isn't the best way to ensure predictable results.
    – AMtwo
    Commented Apr 5, 2017 at 13:56
  • Original post updated. We have a developer team of 20 developers. I would prefer not to tell them to use substring in SQL or truncate in java as both are error prone. I am rather looking for MSSQL not to round timestamps/datetime2 values. I believe majority of databases truncates and not round values. Commented Apr 5, 2017 at 14:23
  • You don't have to use substring method. Doesn't Java provide with an equivalent to datetime(6) type or (dateime) methods to truncate the values to microsecond accuracy? Commented Apr 5, 2017 at 18:17
  • @ypercybe, that is our current workaround. We truncate in java and ensure that we don't send more fraction digits than 6 to MSSQL, then there is no rounding. However as mentioned earlier, if a developer forgets to truncate and sends the default 12 fraction digits, he will create data entries with mismatch in keys across databases, creating big problems down the road. Hence the reason i'd like to ensure in the db layer that timestamps aren't rounded Commented Apr 5, 2017 at 19:02

3 Answers 3


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');

    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
  • The question is how to truncate upon insert, not when selecting existing data though, isn't it?
    – mustaccio
    Commented Apr 14, 2023 at 16:57
  • I'm showing how to do the truncation, which is "the hard part". To avoid SQL's default rounding behavior, you have to load the data into a temporary datetime2(7) (either a variable or a temp table) and then do the calculation to get the less precise datetime2. The subsequent operation (insert or select) is an easy step from there. Commented Apr 14, 2023 at 18:44

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

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)

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

you cannot achieve this without trimming the last digit

  • This will trim '2017-03-28 14:00:59.41' to '2017-03-28 14:00:59.4' though. It will work only if all values are provided with exactly 7 decimal digits. Commented Apr 6, 2017 at 21:33
  • I think that the op was asking to convert from 7 digit to 6 without rounding
    – Hadi
    Commented Apr 6, 2017 at 21:38
  • Yes, your answer solves that. But he also mentions 12 digits so I am not sure how exactly they are sending the values to SQL Server. Commented Apr 6, 2017 at 21:41
  • 1
    @ypercube, We send more digits to MSSQL than it can store, and we want it to truncate rather than round. I believe the conclusion is that it is not possible to do so, there is no such configuration option. There either the code or the SQL statement must manually truncate the value prior to storing it. Commented Apr 7, 2017 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.