Search code examples
sqlsql-server-2008smalldatetime

SQL Server : order of operations not being followed?

[Running SQL Server 2008 SP3]

I have a query which results in the overflow of a smalldatetime type. However, this should (in theory) never happen, due to the way I have the query structured - the logic should cause the truth value to be known long before the DATEADD() is executed which causes the overflow.

Here is the relevant part of the WHERE clause:

TimeIn >= '1/8/1950' AND TimeIn < '1/9/1950' AND
DATEADD(week, DATEDIFF(week, '1/8/1950', '9/14/2014'), TimeIn) >= '9/14/2014'

This works great - except when TimeIn (a smalldatetime) is >= 10/1/2014, then it will overflow the smalldatetime space. But why does DATEADD() even get executed? if the date is 10/1/14, it should never be executed... but it is.

Solution

  • The portions of a WHERE criteria aren't executed in a defined order that prevents your DATEADD() from executing, that's just not how SQL Server works.

    I don't actually see an error when I run your query with the problematic date hard coded, but one way around this would be to use a CASE expression:

    TimeIn >= '1/8/1950' AND TimeIn < '1/9/1950' 
AND CASE WHEN TimeIn >= '1950-01-08' AND TimeIn < '1950-01-09' 
         THEN DATEADD(week, DATEDIFF(week, '1/8/1950', '9/14/2014'), TimeIn) 
    END >= '2014-09-14'