Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--