TSQL Date Conversion Failure - My Inability to understand result sets

I've got a staging area that I'm trying to validate data in, going through multiple iterations of validation. Currently, I'm fighting some issues with a nvarchar(50) column that I'm attempting to convert to a date.

I'm aware of the common pitfall of poorly formed strings failing date conversion, so here's what I'm doing.

SELECT *
  FROM ( SELECT * FROM STAGE_TABLE WHERE ISDATE(DATE_COL) = 1)
 WHERE CAST(DATE_COL AS DATE) < GETDATE()

... this results in the standard "Conversion failed when converting date and/or time from character string."

But here's where things get weird for me. If I change the above statement to the following:

SELECT CAST(DATE_COL AS DATE)
  FROM ( SELECT * FROM STAGE_TABLE WHERE ISDATE(DATE_COL) = 1)

... all is well, and all I've done is moved the cast from the where clause to the select clause. I think I'm missing something at a fundamental level.

FWIW, if I were to pull all r开发者_如何学运维ecords from STAGE_TABLE without the WHERE ISDATE clause, I would have poorly formed date strings.

Any insights greatly appreciated!

You should find that the first query merges the two WHERE clauses into one, and works out the CAST before the ISDATE (fail).

The 2nd query clearly has to process the WHERE first, so the CAST never sees bad data

I have just tested and can verify:

create table STAGE_TABLE ( date_col nvarchar(50) )
insert into STAGE_TABLE select 'a'
insert into STAGE_TABLE select '20100101'

First query

SELECT *
  FROM ( SELECT * FROM STAGE_TABLE WHERE ISDATE(DATE_COL) = 1) X
 WHERE CAST(DATE_COL AS DATE) < GETDATE()

First plan

  |--Filter(WHERE:(isdate([tempdb].[dbo].[STAGE_TABLE].[date_col])=(1)))
       |--Table Scan(OBJECT:([tempdb].[dbo].[STAGE_TABLE]), WHERE:(CONVERT(date,[tempdb].[dbo].[STAGE_TABLE].[date_col],0)<getdate()))

Second query

SELECT CAST(DATE_COL AS DATE)
  FROM ( SELECT * FROM STAGE_TABLE WHERE ISDATE(DATE_COL) = 1) X

Second plan

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(date,[tempdb].[dbo].[STAGE_TABLE].[date_col],0)))
       |--Filter(WHERE:(isdate([tempdb].[dbo].[STAGE_TABLE].[date_col])=(1)))
            |--Table Scan(OBJECT:([tempdb].[dbo].[STAGE_TABLE]))

There does not seem to be a hint/option to fix the first query (since it gets rolled into one WHERE clause), but you can use this which processes both conditions in one scan pass.

SELECT *
  FROM (SELECT * FROM STAGE_TABLE) X
 WHERE CAST(CASE WHEN ISDATE(DATE_COL) = 1 THEN DATE_COL ELSE NULL END AS DATE) < GETDATE()