varchar to datetime conversion failure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

varchar to datetime conversion failure

Author

Topic

dlh
Starting Member

28 Posts

Posted - 2010-06-23 : 16:27:36

I've got a DELETE query that fails with a "failed to convert datetime from character string" error. But if I use the exact same query with SELECT in place of DELETE, it runs fine (and identifies the rows I wish to delete).

I am comparing a DATETIME column to a VARCHAR column (which contains date strings) and I know there are a few non-convertible strings, but I thought the CASE statement shielded them.

Here's a simplified version of the query:


delete
    di
from
    MyDatabase.dbo.DocumentIndex as di
where
    di.IndexType = @OldIndexType
    and exists
    (
        select *
        from MyDatabase.dbo.DocumentIndex di2
        where 
            di2.DocumentID = di.DocumentID
	    and di.IndexValue = 
                    case isdate(di2.IndexValue)
                        when 1
                            then cast(di2.IndexValue as datetime)
                        else
                            0
                        end
            and di2.IndexType = @NewIndexType
    )

Why should this work with a SELECT but not a DELETE?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-23 : 16:50:26

Try a condition like this:


case  when isdate(di.IndexValue)  = 1 then cast(di.IndexValue  as datetime) else 0 end =
case  when isdate(di2.IndexValue) = 1 then cast(di2.IndexValue as datetime) else 0 end

CODO ERGO SUM

dlh
Starting Member

28 Posts

Posted - 2010-06-23 : 17:36:06

Thank you, Michael. Shielding the other IndexValue with a CASE statement was necessary.

The curious question should have been: why did the SELECT actually work before (rather than why did the DELETE not work)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources