Hi all,
I am thinking of opening a case with Microsoft unless someone else has
seen this bug before (or knows a quick fix, BESIDES using a temporary
table).

DECLARE @T TABLE (CHECKDATE VARCHAR(50))

INSERT INTO @T SELECT '01/01/2005'
INSERT INTO @T SELECT '02-02-206' -- bad value
INSERT INTO @T SELECT '03-03-2005'
INSERT INTO @T SELECT '04-04-2005'
INSERT INTO @T SELECT '05-05-2005'

SELECT A.CHECKDATE FROM
(
SELECT CHECKDATE FROM @T
WHERE ISDATE(CHECKDATE)=1
) A
WHERE CAST(A.CHECKDATE AS SMALLDATETIME) BETWEEN CAST('2005-02-02
00:00:00' AS SMALLDATETIME) AND CAST('2005-04-04 23:59:59' AS
SMALLDATETIME)

If you remove the last "WHERE" clause the results return without the
bad value; but if you include the final "WHERE" clause you get:
Server: Msg 296, Level 16, State 3, Line 34
The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value.

Now go back and fix the bad value (make it 2006) and rerun the query
with the "WHERE" clause and the results return normally. The problem is
that the derived table is not being run first. This seems like a bug to
me.

Josh

It's not a bug, since the optimizer can rewrite a query and execute it
in whatever order it finds most efficient - there's no reason why it
should retrieve the derived table first. In TSQL, you specify what
results you want, and let the optimizer work out the most efficient way
to get them.

The real issue is that you're trying to treat dirty data as if it were
clean, and that's unlikely to be a reliable solution - better to clean
it up, put it in a datetime then query it. But the following query
should work for you:

DECLARE @T TABLE (
CHECKDATE VARCHAR(50) primary key,
cleandate as cast(case isdate(checkdate) when 1 then checkdate else
'99991231' end as datetime)
)

INSERT INTO @T SELECT '01/01/2005'
INSERT INTO @T SELECT '02-02-206' -- bad value
INSERT INTO @T SELECT '03-03-2005'
INSERT INTO @T SELECT '04-04-2005'
INSERT INTO @T SELECT '05-05-2005'

SELECT *
FROM @T
WHERE cleandate >= '20050202' and cleandate < '20050405'

Incidentally, your logic seems to be incorrect, in that you've
specified that you want dates less than or equal to '2005-04-04
23:59:59', but smalldatetime is rounded to the nearest minute, so you
will include 20050505 in your results. Datetime is also rounded (to
3ms), which is why it's often safer to avoid BETWEEN with datetime and
use >< instead.

Having said all that, I personally would clean up the data instead of
using a query like the one above, but obviously I don't know exactly
what you're trying to do, why you can't clean up the data, why you want
to avoid using a permanent or temporary table etc.

Finally, see here for some background on working with datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon

Simon, thanks for the help.

I took your solution and tailored it to fit my needs:

DECLARE @T TABLE (CHECKDATE VARCHAR(50))

INSERT INTO @T SELECT '01/01/2005'
INSERT INTO @T SELECT '02-02-206' -- bad value
INSERT INTO @T SELECT '03-03-2005'
INSERT INTO @T SELECT '04-04-2005'
INSERT INTO @T SELECT '05-05-2005'

SELECT CAST(CHECKDATE AS DATETIME) FROM @T
WHERE CAST(CASE ISDATE(CHECKDATE) WHEN 1 THEN CHECKDATE ELSE
'9999-12-31' END AS DATETIME)
BETWEEN CAST('2005-02-02' AS DATETIME) AND CAST('2005-04-04' AS
DATETIME)