T-SQL error: Adding a value to a 'smalldatetime' column caused overflow. SQL server bug?

Question

When I try to execute the following code, I get error "Adding a value to a 'smalldatetime' column caused overflow":

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

As you can see, result of DATEADD() cannot be less than minimal value. Moreover, DATEADD() must not be called, because SELECT must return nothing. Is it an SQL server bug?

Answer 1

The smalldatetime value has the following date range:

1900-01-01 through 2079-06-06

January 1, 1900, through June 6, 2079

You are getting the error because your calculation is leading to date out of this boundaries:

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

The questions is why the SELECT stuff is executed before the FROM, JOIN and WHERE stuff, as this is not the Logical Processing Order of the SELECT statement.

If you change the 19000101 value to 19000102, just to get the execution plan the SQL engine is building, you should see this:

The Compute Scalar is actually the DATEADD function execution:

So, the DATEADD function is executed and throws an error. This is caused because:

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

You can use the OPTION(FORCE ORDER) to change the execution plan and make your query work:

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)