sql-server - T-SQL 错误 : Adding a value to a 'smalldatetime' column caused overflow. SQL 服务器错误？

Question

当我尝试执行以下代码时，出现错误“将值添加到‘smalldatetime’列导致溢出”:

DECLARE @t TABLE (ID UNIQUEIDENTIFIER, BegDate SMALLDATETIME, EndDate SMALLDATETIME)

INSERT INTO @t
SELECT NEWID(), '19000101', '20151124'

-- This is to show you that nothing is really selected
SELECT 'You will never see it'
FROM @t r1
INNER JOIN @t r2 ON r2.ID = r1.ID
WHERE   1=1
    AND r1.BegDate > r2.BegDate    
    AND r1.EndDate <= r2.EndDate

SELECT r1.ID, r2.BegDate, DATEADD(DAY, -1, r1.BegDate) AS EndDate
FROM @t r1
INNER JOIN @t r2 ON r2.ID = r1.ID
WHERE   1=1
    AND r1.BegDate > r2.BegDate  -- This guarantees that r1.BegDate is not minimal value
    AND r1.EndDate <= r2.EndDate

如您所见，DATEADD() 的结果不能小于最小值。此外，不得调用 DATEADD()，因为 SELECT 必须不返回任何内容。是 SQL Server 错误吗？

Answer 1

smalldatetime值具有以下日期范围:

1900-01-01 through 2079-06-06

January 1, 1900, through June 6, 2079

您收到错误是因为您的计算导致日期超出此边界:

DECLARE  @BegDate SMALLDATETIME = '19000101'
SELECT @BegDate
SELECT DATEADD(DAY, -1, @BegDate)

问题是为什么 SELECT 会在 FROM、JOIN 和 WHERE 之前执行，因为这不是 Logical Processing Order of the SELECT statement .

如果您将 19000101 值更改为 19000102，只是为了获取 SQL 引擎正在构建的执行计划，您应该会看到:

Compute Scalar其实就是DATEADD函数执行:

因此，DATEADD 函数被执行并抛出错误。这是因为:

Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

---

SELECT r1.ID, r2.BegDate, IIF(r1.BegDate = '19000101', NULL, DATEADD(DAY, -1, r1.BegDate)) AS EndDate
FROM @t r1
INNER JOIN @t r2 ON r2.ID = r1.ID
WHERE   1=1
    AND r1.BegDate > r2.BegDate  -- This guarantees that r1.BegDate is not minimal value
    AND r1.EndDate <= r2.EndDate

您可以使用OPTION(FORCE ORDER) 来更改执行计划并使您的查询工作:

SELECT r1.ID, r2.BegDate, DATEADD(DAY, -1, r1.BegDate) AS EndDate
FROM @t r1 
INNER JOIN @t r2 ON r2.ID = r1.ID
WHERE   1=1
    AND r1.BegDate > r2.BegDate  -- This guarantees that r1.BegDate is not minimal value
    AND r1.EndDate <= r2.EndDate
OPTION  (FORCE ORDER)