SELECT *
FROM
(
    SELECT *
    FROM MyTable
    WHERE IsDate(StringColumn) = 1
) AS X
WHERE CONVERT(datetime, StringColumn) > '20070101'

quote:

---

Originally posted by Kristen

Damn good thing if you ask me, StringColumn should be called DateColumn

Kristen

---

quote:

---

Originally posted by Kristen

Actually I suppose its the same as

WHERE IsDate(StringColumn) = 1 AND CONVERT(datetime, StringColumn) > '20070101'

this is BOUND to fail because it admits [to me anyway!] that the table contains invalid dates

Mind you, I'm pretty sure I've been able to get away with this by doing:

SELECT *
FROM
(
    SELECT *
    FROM MyTable
    WHERE IsDate(StringColumn) = 1
) AS X
WHERE CONVERT(datetime, StringColumn) > '20070101'

but maybe I won't be able to under SQL 2005?

Damn good thing if you ask me, StringColumn should be called DateColumn

Kristen

---

select
	*
from
	(
	select Number = 1, StringColumn = 'xxxx0101' union all
	select Number = 2, StringColumn = '20070101' union all
	select Number = 3, StringColumn = '20070102' union all
	select Number = 4, StringColumn = null union all
	select Number = 5, StringColumn = '20070131' union all
	select Number = 6, StringColumn = '20070132' union all
	select Number = 7, StringColumn = '20070228' union all
	select Number = 8, StringColumn = '20070229' union all
	select Number = 9, StringColumn = '20080229'
	) MyTable
where
	case
	when IsDate(StringColumn) <> 1
	then 0
	when convert(datetime,StringColumn) > '20070101'
	then 1
	else 0
	end = 1
order by
	Number

Results:

Number      StringColumn 
----------- ------------ 
3           20070102
5           20070131
7           20070228
9           20080229

(4 row(s) affected)

quote:

---

Originally posted by Kristen

Is convert(datetime,StringColumn) guaranteed not to be calculated if IsDate(StringColumn) <> 1?

I did try an example indexing StringColumn (even using a Clustered index), in case that got shortcircuited, but it seemed OK on this small data set.

(Query Plan was a Scan using the Clustered Index)

Kristen

---