none
遇到一个datetime2出现的BUG RRS feed

  • 问题

  • update_time的类型是datetime

    为什么我加了条件and update_time<=cast('2021-07-14 13:34:25.503' as datetime2)  ,查不出数据来,理论上不是应该

    '2021-07-14 13:34:25.503'  =  '2021-07-14 13:34:25.5030000'的吗

    我改成datetime就可以了,但是我的参数可能是datetime2

    为什么会出现这种情况



    2021年7月14日 5:55

答案

  • DateTime字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fff ,3个f,精确到1毫秒(ms),示例 2014-12-03 17:06:15.433;
    DateTime2字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fffffff ,7个f,精确到0.1微秒(μs),示例 2014-12-03 17:23:19.2880929 ;

    DateTime精确到1毫秒,'2021-07-14 13:34:25.503'  as datetime是没有问题的,

    为什么'2021-07-14 13:34:25.503'  as datetime2就不行了,这里涉及到计算机内部存储机制,即二进制浮点数的存储。

    '2021-07-14 13:34:25.503'格式中年、月、日比较'1900/1/0'的差值作为日期浮点数的整数部分,小时、分、秒、毫秒作为日期浮点数的小数部分,

    计算机中浮点数的存储与整数不同,按照1/2、1/4、1/8等累加合成,因此这个浮点值是存在误差的。

    select语句匹配等式要求两个比较值整数部分和浮点部分绝对相等,这样就会造成datetime2字段类型等式匹配不上的情况。


    2021年7月14日 8:07
  • datetime数据类型的1ms换算成浮点数约为0.000000011574,直接将这个值作为增量确定误差范围即可。
    2021年7月15日 3:35

全部回复

  • 目前我只能针对这种情况,只能手动增加一毫秒临时处理,太奇怪了感觉是精度问题

    DATEADD(ms,1,cast('2021-07-14 13:34:25.503' as datetime2))

    2021年7月14日 6:07
  • 你好,

    datetime2默认精度为 7 位。

    可以指定精度(可能导致精度丢失),比如:

    select cast('2021-07-14 13:34:25.503123' as datetime2(3))


    2021年7月14日 7:17
  • 你好,

    datetime2默认精度为 7 位。

    可以指定精度(可能导致精度丢失),比如:

    select cast('2021-07-14 13:34:25.503123' as datetime2(3))


    d我的意思是为什么2021-07-14 13:34:25.503的数据不等于2021-07-14 13:34:25.5030000,从而导致我<=时没有匹配出来
    2021年7月14日 7:43
  • 但是datetime和datetime2比较不一样

    --返回2 false

    select iif(cast('2021-07-14 13:34:25.503' as datetime)=cast('2021-07-14 13:34:25.5030000' as datetime2),1,2)

    2021年7月14日 8:01
  • DateTime字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fff ,3个f,精确到1毫秒(ms),示例 2014-12-03 17:06:15.433;
    DateTime2字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fffffff ,7个f,精确到0.1微秒(μs),示例 2014-12-03 17:23:19.2880929 ;

    DateTime精确到1毫秒,'2021-07-14 13:34:25.503'  as datetime是没有问题的,

    为什么'2021-07-14 13:34:25.503'  as datetime2就不行了,这里涉及到计算机内部存储机制,即二进制浮点数的存储。

    '2021-07-14 13:34:25.503'格式中年、月、日比较'1900/1/0'的差值作为日期浮点数的整数部分,小时、分、秒、毫秒作为日期浮点数的小数部分,

    计算机中浮点数的存储与整数不同,按照1/2、1/4、1/8等累加合成,因此这个浮点值是存在误差的。

    select语句匹配等式要求两个比较值整数部分和浮点部分绝对相等,这样就会造成datetime2字段类型等式匹配不上的情况。


    2021年7月14日 8:07
  • DateTime字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fff ,3个f,精确到1毫秒(ms),示例 2014-12-03 17:06:15.433;
    DateTime2字段类型对应的时间格式是 yyyy-MM-dd HH:mm:ss.fffffff ,7个f,精确到0.1微秒(μs),示例 2014-12-03 17:23:19.2880929 ;

    DateTime精确到1毫秒,'2021-07-14 13:34:25.503'  as datetime是没有问题的,

    为什么'2021-07-14 13:34:25.503'  as datetime2就不行了,这里涉及到计算机内部存储机制,即二进制浮点数的存储。

    '2021-07-14 13:34:25.503'格式中年、月、日比较'1900/1/0'的差值作为日期浮点数的整数部分,小时、分、秒、毫秒作为日期浮点数的小数部分,

    计算机中浮点数的存储与整数不同,按照1/2、1/4、1/8等累加合成,因此这个浮点值是存在误差的。

    select语句匹配等式要求两个比较值整数部分和浮点部分绝对相等,这样就会造成datetime2字段类型等式匹配不上的情况。


    d您好,请教下我这种情况,怎么处理合适,我的表机构是datetime,而传入的值可能是datetime可能是datetime2
    2021年7月14日 10:18
  • 你好:

        感谢在论坛发贴提问,你的SQL语句中加入条件是可以的,但在比较datetime2字段类型时尽量不用等号,通过调大调小参数定值1ms,或调整毫秒精度的位数,或将datetime2类型先转成datetime再传入。


    2021年7月14日 12:19
  • 你好:

        感谢在论坛发贴提问,你的SQL语句中加入条件是可以的,但在比较datetime2字段类型时尽量不用等号,通过调大调小参数定值1ms,或调整毫秒精度的位数,或将datetime2类型先转成datetime再传入。


      好的,我发现毫秒更新都会出现精度问题,1ms不是稳妥的精度,比如2014-01-01 11:30:31.678  我更新成字符串2014-01-01 11:30:31.679,可能会变成2014-01-01 11:30:31.681 ,因为我涉及到增量同步,我目前改成+10ms,暂时没发现其他问题
    2021年7月15日 3:04
  • datetime数据类型的1ms换算成浮点数约为0.000000011574,直接将这个值作为增量确定误差范围即可。
    2021年7月15日 3:35
  • datetime数据类型的1ms换算成浮点数约为0.000000011574,直接将这个值作为增量确定误差范围即可。
    d多谢了,那是不是说明1ms足以。
    2021年7月15日 5:07
  • 其实浮点数产生的误差是相当微小的,普通数值计算中可以忽略不计的,但浮点数值比较时慎用等号,除0.5、0.25、0.125等二进制能精确表示的值。
    2021年7月15日 8:43
  • 参考官方文档说明:

    datetime2 (Transact-SQL) - SQL Server | Microsoft Docs

    将其他日期和时间类型转换为 datetime2 数据类型

    从 datetime 转换时,会复制日期和时间。 小数精度扩展到 7 位。

    然后,datetime 的精度是千分之三秒,将这个精度扩展到7位,那么503毫秒这个对到7位小数,就变成了5033333

    你可以用 CAST(CAST('2021-07-14 13:34:25.503'  as datetime) as datetime2) 来验证

    2021年7月20日 4:40
  • 参考官方文档说明:

    datetime2 (Transact-SQL) - SQL Server | Microsoft Docs

    将其他日期和时间类型转换为 datetime2 数据类型

    从 datetime 转换时,会复制日期和时间。 小数精度扩展到 7 位。

    然后,datetime 的精度是千分之三秒,将这个精度扩展到7位,那么503毫秒这个对到7位小数,就变成了5033333

    你可以用 CAST(CAST('2021-07-14 13:34:25.503'  as datetime) as datetime2) 来验证

    多谢有案例和官方文档更直观了
    2021年7月20日 5:19
  • datetime 的精度是千分之三秒,将这个精度扩展到7位,那么503毫秒这个对到7位小数,就变成了5033333

         

          根据参考官方datetime (Transact-SQL)文档,datetime 的精度仍表示为千分之一秒,但精确度舍入到 .000、.003 或 .007 秒三个增量。


    2021年7月20日 5:45