SQL Server: Function Evaluation Time

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开发者_StackOverflow 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.

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

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

Looking at your query you are actively saying len(inception_date27)!=8, so you probably expect no results to show up.

But, you also specify that you know there are values of '0' in there. Those are being returned because 0!=8. So the join does appear to return results.

Try replacing len(inception_date27)!=8 with (0=1) to see what happens then. That should definitely not return any results.

Without knowing the exact details of how how SQL-Server works internally to execute queries, my guess for such a behaviour is that it uses parallel processes or threads to execute it.

So, there may be 3 threads, one checking polmst.policy_no2 = cast(dtl.arch_master_policy_no41 as numeric), another checking len(inception_date27) != 8 while the main process is gathering the results and sending rows that match to the 3rd process that is doing the conversion DATEPART(yyyy,cast(inception_date27 as datetime)).

This would NOT result in the above error. But the appearence of both CAST() and LEN() in the above conditions forbid the use of indexes in the related fields. So, perhaps (that's my wild guess), the 1st process is slower than the 2nd and the main process is sending results from the 2nd thread to the 3rd, just in case they are matched by the 1st.

It's quite possible that such behaviour is the usual way the database runs queries. You just haven't happened to see it before. It's quite possible that Oracle behaves that way as well.

The inception_date27 field is in the [H5_PREMIUM_DETAIL] (dtl) table, right?

Can you write the query as below and check it same error is raised? It may still do as a subquery does not force order of execution either.

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

Use the ISDATE function in your WHERE clause to make sure you have a valid date string.