4

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.

  • 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 Apr 5 '17 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 Apr 5 '17 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. – Kelvin Wayne Apr 5 '17 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? – ypercubeᵀᴹ Apr 5 '17 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 – Kelvin Wayne Apr 5 '17 at 19:02
2

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
1

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

  • 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. – ypercubeᵀᴹ Apr 6 '17 at 21:33
  • I think that the op was asking to convert from 7 digit to 6 without rounding – Hadi Apr 6 '17 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. – ypercubeᵀᴹ Apr 6 '17 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. – Kelvin Wayne Apr 7 '17 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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