January 13, 2006 at 2:24 pm
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.
January 16, 2006 at 8:00 am
This was removed by the editor as SPAM
May 2, 2006 at 12:57 pm
I'm getting a similar conversion error converting birthdates. A SELECT that was working fine in 2000 returns an error in 2005.
SELECT Birthday,
CONVERT(datetime,
CASE
WHEN CONVERT(CHAR(5),Birthday,1)='02/29' THEN '02/28/'
ELSE CONVERT(CHAR(6),Birthday,1)
END + '2006')
FROM Customers
WHERE Birthday IS NOT NULL
This converts a birthday to this year. CONVERT() returns mm/dd/yy. If you add TOP 1000000 to the SELECT it works fine on 2005, which really makes no sense.
Randy
May 3, 2006 at 1:12 am
A J,
Try specifying the format for the date in your convert statement and try casting to a specific type e.g.
SELECT col_names
FROM tablename
WHERE
CAST(T_DATE as DATETIME) < CONVERT(datetime,dateadd(d,7, getdate()),103)
Also make sure that the date you are adding is valid, maybe print out dateadd(d,7, getdate()) (note any dates below 1750 or something are unrecognised by SQL server) to see what you get?
Ed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply