建模提醒功能异常,获取查询语句到数据库执行报错:
Msg 535, Level 16, State 0, Line 62 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. 消息535,级别16,状态0,第62行
经排查数据有俩问题:
1.日期字段很多是空的。
2.有几个日期是2099年的。
datediff这个值溢出的情况。在 官方文档 中,datediff函数定义返回的是int值,int值的取值范围是 (-2,147,483,648 to +2,147,483,647)。
SQL Server报错The datediff function resulted in an overflow 可以看到,如果是到秒级,即datediff(ss),中间的时间差是可以长达68年19天3小时14分7秒的。
SQL Server报错The datediff function resulted in an overflow
也就是说,在为null的情况下,这个datetime类型的值,将有默认值来填充,所以也就出现了1900-01-01 00:00:00.000。
那么sysprocesses的last_batch会出现控制,进而被替代成1900-01-01 00:00:00.000 ?
SQL Server报错The datediff function resulted in an overflow
However, it's possible to create a connection to SQL Server without issuing any RPC calls at all. In this case, the value of last_batch will never have been set and master..sysprocesses will display the value as 1900-01-01 00:00:00.000. 也就是说,由非远程调用(RPC,remote procedure call)发起的进程,其last_batch是null值,而null值继而会被1900-01-01 00:00:00.000所替代。
扩展测试:
Mysql数据库下没这问题:
select TIMESTAMPDIFF(SECOND,'2099-10-31 00:00:00','2023-01-01 00:00:00')
标签:function,00,01,datediff,Server,报错,SQL From: https://blog.51cto.com/mflag/9186540