Datetime2 is more accurate than datetime which has only accuracy for milliseconds.
From
datetime2 (Transact-SQL)[
^]:
When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.
The following example shows the results of converting a datetime2(4) value to a datetime value.
DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';
DECLARE @datetime datetime = @datetime2;
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
What comes to the value not being 881 milliseconds this happens because of the accuracy of datetime. Datetime is not accurate on millisecond level but Accuracy is rounded to increments of .000, .003, or .007 seconds;
You can visualize this with a small loop:
DECLARE @DT DATETIME
DECLARE @DT2 DATETIME2
DECLARE @counter INT = 0
SET @DT2='2015-07-23 18:35:34.8815038'
WHILE @counter < 10 BEGIN
SELECT @DT=@DT2
SELECT @DT2 AS [DATETIME2],@DT AS [DATETIME];
SET @DT2 = DATEADD(ms, 1, @DT2)
SET @counter = @counter + 1
END
For more information, see
datetime (Transact-SQL)[
^]