0

[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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Conrad
  • 2,197
  • 28
  • 53

1 Answers1

3

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'
Andriy M
  • 76,112
  • 17
  • 94
  • 154
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • I was afraid of that. Any suggestions for a(n elegant) way around this? – Conrad Sep 08 '14 at 19:20
  • I actually don't see what the problem is, you said it occurs when the date is after `10/1/2014` but the following seems to run fine: `SELECT DATEADD(week, DATEDIFF(week, '1/8/1950', '9/14/2014'), '10/2/2014')` – Hart CO Sep 08 '14 at 19:26
  • You can try parentheses around each expression part and hope SQL will short-cut it, but there is no guarantee as to how the optimizer will evaluate the query – Sparky Sep 08 '14 at 19:28
  • @GoatCO yes your example works. But in my (larger) query, the SQL interpreter is deciding to keep the intermediate result in a `smalldatetime`. If you wrap your above `DATEADD()` in a `CAST( AS smalldatetime)` then you see the problem. – Conrad Sep 08 '14 at 19:38
  • 2
    @Conrad Ah, well instead of the `CASE` statement you could `CAST()` to a different datatype the offending field, not sure which is simpler. – Hart CO Sep 08 '14 at 19:43