CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2sql-server-2012
Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1384

Unexpected rounding-off from DATETIME2 to DATETIME

First of all, it may not have a practical use but just want to know how/why this is happening.

When I am trying to assign a DATETIME2 value to a DATETIME variable, there happens an unexpected roundoff in the value.

Sample SQL :

DECLARE @DT DATETIME
DECLARE @DT2 DATETIME2

SET @DT2='2015-07-23 18:35:34.8815038'
SELECT @DT=@DT2

SELECT @DT2 AS [DATETIME2],@DT AS [DATETIME]

OUTPUT :

DATETIME2                             DATETIME
------------------------------------------------------------        
2015-07-23 18:35:34.8815038    |     2015-07-23 18:35:34.883

Now, I am not able to find the cause why 8815038 nano seconds is rounded off to 883 milliseconds against expected 881 or 882 milliseconds.

If any body knows the exact explaination to this, please share here. :-)

Upvotes: 2

Views: 456

Answers (1)

AdamL
AdamL

Reputation: 13141

The reason is that in datetime type, you only have .000, .003 and .007 fraction of a second as smallest increments.

Try this: 

select cast('2015-01-01 00:00:00.882' as datetime)
select cast('2015-01-01 00:00:00.884' as datetime)

And you'll see that both are rounded to .883

Upvotes: 4

Related Questions