DateAdd Column 导致溢出 [英] DateAdd Column caused an overflow

问题描述

执行以下查询后出现错误

After executing the following query I am getting an error

向日期时间"列添加值会导致溢出.

Adding a value to a 'datetime' column caused an overflow.

我不知道为什么会发生这种情况，因为它运行了几个星期.我只是想在这里添加日期，并使用 between 子句将它们与开始日期和结束日期进行比较.

I have no idea why this is happening as it worked smoothly for a couple of weeks. I am just trying to ADD Dates here and compare them to the Start date and End date with a between clause.

DATEADD(day, -1 , DATEADD(mm, DATEDIFF(mm,0,posting_date),0)) BETWEEN start_date and end_date

推荐答案

我可以重复以下错误:

declare @posting_date datetime
set @posting_date = '1/1/1753'

select DATEADD(day, -1 , DATEADD(mm, DATEDIFF(mm,0,@posting_date),0)) 

运行后报错:

消息 517，级别 16，状态 1，第 3 行向日期时间"添加值列导致溢出.

Msg 517, Level 16, State 1, Line 3 Adding a value to a 'datetime' column caused an overflow.

基本上，上述情况中的posting_date 是SQL Server 允许的最小日期时间值.如果您随后尝试从中减去 1 天，则会进入溢出条件.

Basically, posting_date in the above case is the minimum date time value allowed by SQL server. If you then try to subtract 1 day from it, then it enters an overflow condition.

我的猜测是您有一个数据点设置为 SQL Server 的最小日期值.

My guess is that you have a datapoint which is set to SQL Server's minimum date value.

这篇关于DateAdd Column 导致溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！