首页 > 数据库 >SQL Server报错The datediff function resulted in an overflow

SQL Server报错The datediff function resulted in an overflow

时间:2024-01-10 22:01:04浏览次数:26  
标签:function 00 01 datediff Server 报错 SQL

建模提醒功能异常,获取查询语句到数据库执行报错:

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')

SQL Server报错The datediff function resulted in an overflow_SQL

标签:function,00,01,datediff,Server,报错,SQL
From: https://blog.51cto.com/mflag/9186540

相关文章