SQL Server: Function Evaluation Time

Question

SQL Server 2005:

Query below returns no data ((0 row(s) affected)), OK.

But when Line #3 function call is enabled, the query fails!

Conversion failed when converting datetime from character string.

Coming from Oracle background - This is just unthinkable!

Please tell me what's going on. Am I doing it wrong ?

Doesn't SS evaluate function calls in SELECT list at the last step (like Oracle does - or any sensible programmer would do)?

select  
   1 as one
   --,DATEPART(yyyy,cast(inception_date27 as datetime)) as inception_yr
 from [DBO].[H5_PREMIUM_DETAIL] dtl,
      [DBO].[H5_POLICY_MASTER] polmst 
  where polmst.policy_no2 = cast(dtl.arch_master_policy_no41 as numeric)      
  and len(inception_date27) != 8

Here is a small clip I recorded

---

Note: I know the root SOURCE of the bad data (some rows have "0" instead of "20110704") that SS complains about here - the gist is why this failure arise where no data found from the JOIN.

Answer 1

Doesn't SS evaluate function calls in SELECT list at the last step (like Oracle does - or any sensible programmer would do) ?

No. (and - No).

The general rule in SQL is that there's a defined "order" in which the parts of the query should appear to be processed, but that different database systems are free to re-order those operations, provided the end result is the same.

However, there's still some room for ambiguity, and SQL Server seems more likely than most systems to push conversions earlier in the process than might otherwise be expected - it may be that, given the IO overhead of the operations, and the expense of performing the join operation (how big are the tables? What type of join has the optimizer selected?), that performing the conversion operation before the join occurs is practically free, so that's what the optimizer has chosen to do.

It would be nice if SQL Server could mark such conversion failures in the rows as they're processed, and only report at the end if the result set still contains a row with a conversion failure, but that's not how it currently works.

Pushing all of it into a CASE expression in the select clause would probably be enough (similar to niktrs answer):

SELECT
   /* Other columns */
   CASE
       WHEN inception_date27 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
           THEN DATEPART(year,CONVERT(datetime,inception_date27))
   END

Answer 2

Maybe you should have LEN equal to 8 not len different to 8

Replace

len(inception_date27) != 8

With

len(inception_date27) = 8

An alternative way is to use case for the date, when not valid then return NULL

Eg.

DECLARE @mydate VARCHAR(8)

SET @mydate = '20100708'

SELECT CASE WHEN LEN(@mydate) = 8 THEN DATEPART(yyyy,CAST(@mydate AS datetime)) ELSE NULL END 
--WHERE LEN(@mydate) <> 8