从DATETIME2到DATETIME的意外舍入

本文介绍了从DATETIME2到DATETIME的意外舍入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 限时删除！！ 首先，它可能没有实际用途但只是想知道如何/为何发生这种情况。 当我尝试分配DATETIME2时值为DATETIME变量时，值会发生意外的舍入。 示例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 ] 输出： DATETIME2 DATETIME 2015-07-23 18：35：34.8815038 | 2015-07-23 18：35：34.883 现在，我无法找到原因 8815038 881或 毫秒，i>纳秒被舍入到 883 毫秒。 如果任何机构知道对此的确切解释，请在此处分享。 ：）解决方案 Datetime2比datetime更准确，只有毫秒精度。 来自 datetime2（Transact-SQL） [ ^ ]： 当小数精度时如果datetime2（n）值大于三位数，则该值将被截断。 以下示例显示将datetime2（4）值转换为日期时间值的结果。 DECLARE @ datetime2 datetime2（ 4 ）= ' 1968-10-23 12：45：37.1237'; DECLARE @ datetime datetime = @ datetime2 ; SELECT @ datetime AS ' @ datetime'， @ datetime2 AS ' @ datetime2' ; - 结果 - @ datetime @ datetime2 - ----------------------- -------------------- ---- - 1968-10-23 12：45：37.123 1968- 10-23 12：45：37.1237 - - （1行受影响） 由于datetime的准确性，这种情况发生的值不是881毫秒。日期时间在毫秒级别上不准确，但准确度四舍五入到.000，.003或.007秒的增量; 您可以通过一个小循环显示： 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 有关详细信息，请参阅 datetime（Transact-SQL） [ ^ ] 我来了 解决方案。我在 Stackoverflow 中发布了相同的问题[ ^ ] Quote：原因是在datetime类型中，你只有.000，.003和.007分数。 试试这个： 选择 cast（' 2015-01-01 00：00：00.882' as datetime ） 选择 cast（' 2015-01 -01 00：00：00.884' as datetime ） 你会看到两者都四舍五入到.883 日期时间的精度四舍五入到.000，.003或.007秒的增量。 参考： https://msdn.microsoft.com/en-us/library/ms187819.aspx [ ^ ] 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 DATETIMEDECLARE @DT2 DATETIME2SET @DT2='2015-07-23 18:35:34.8815038'SELECT @DT=@DT2SELECT @DT2 AS [DATETIME2],@DT AS [DATETIME]OUTPUT :DATETIME2 DATETIME2015-07-23 18:35:34.8815038 | 2015-07-23 18:35:34.883Now, 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. :) 解决方案 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';--Result--@datetime @datetime2------------------------- --------------------------1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237----(1 row(s) affected)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 DATETIMEDECLARE @DT2 DATETIME2DECLARE @counter INT = 0SET @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 + 1ENDFor more information, see datetime (Transact-SQL)[^]Here I got the solution. I have posted the same question in Stackoverflow[^]Quote:The reason is that in datetime type, you only have .000, .003 and .007 fraction of a second.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 .883Accuracy of datetime is rounded to increments of .000, .003, or .007 seconds.Reference:https://msdn.microsoft.com/en-us/library/ms187819.aspx[^] 这篇关于从DATETIME2到DATETIME的意外舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..