Home Dashboard Directory Help
Search

Msg 241, Level 16 - Conversion failed when converting datetime from character string by Aneesh Aravind


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 498804
Opened: 10/20/2009 9:44:14 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

I have a table which contains a column that is of datatype VARCHAR(256). In this table I have string values and valid datetime strings in the format 'YYYY-MM-DD HH:MM:SS.mmm'. I wish to query for date after having eliminated all those records where the column is not a date. I used ISDATE() to remove the non-date values and then I try to SELECT those records that are older than today.

When I have a subquery, my expectation is that it gets evaluated in the order that it was specified. I have no problem with the Query Optimizer clubs all my WHERE clauses willy nilly, but if I have a view or a subquery, don't you think you should maintain the order?

Everything below this line refers to the implementation code given in "Steps to Reproduce"

Attempts 1 2 and 3 were lame. I fully understand why these 3 didn't work.

But Attempt 4 explicitly calls out that I want to apply the ISDATE() function first and then and only then apply the < GETDATE() predicate.

Same thing with Attempt 5. You would think that when a user has gone to the level of creating a view, that the data returned from the view will only contain dates. How else is one to partition tables horizontally if you are going to apply the WHERE clause to within the view?

Attempt 6 uses subqueries for IN. According to BOL, this should work, but doesn't.

Attempt 7 is one way of getting this accomplished, but I hate using temp tables - especially since the end users usually don't have rights to create temp tables.

Attempt 8 is Microsoft's official "best" response. This was given to me by the Tech who worked on this issue with me. Case #: REG:109101469019749.

As a side note, I fully understand the fact that a column should not contain dates mixed in with other values. This is an audit table and is auditing column value changes for before and after. Sometimes the column that changed happens to be a date column in which case, the column will contain a valid date format.

Thanks in advance,
Aneesh Aravind.
Details
Sign in to post a comment.
Posted by Microsoft on 11/4/2009 at 10:45 AM
Seems like no further discussion on this one, so closing as "by-design".

Thanks,

Jim
Posted by Microsoft on 11/4/2009 at 10:45 AM
Seems like no further discussion on this one, so closing as "by-design".

Thanks,

Jim
Posted by Microsoft on 10/27/2009 at 2:00 PM
Hi,

Thankyou for this report. And the careful analysis. The issue resolves to the order in which SQL-Server evaluates predicates.

So, for example, if we write:
SELECT * FROM Tab WHERE pred1 AND pred2
you might expect that pred1 is evaluated before pred2. Moreover, that if pred1 returns false, then pred2 is not evaluated ("short-circuiting").

However, evaluation order in SQL-Server is implementation-defined. And in those cases which you mark as "Failure", the optimizer performs a Table Scan with the predicate of (TestColumn < GETDATE()) which causes the crash on row 2. It would have gone on to a filter, with predicate ISDATE(TestColumn) - except it never gets that far!

Creating a Temp Table (your example 7) does the job.

"But why?" you are probably asking, does SQL-Server 'play around' with the query? Why does it not simply execute what I tell it (like C, or C++, for example)? And the answer is that the entire engine is tuned for performance - it optimizes any query, so that, so long as conservative assumptions hold good (eg: implicit conversion of VARCHAR to datetime always succeeds), you get your results much faster than otherwise.

So the results are "by-design".

I'll hold off resolving/closing this bug, to leave the thread open for further discussions.

Thanks,

Jim Hogg
Sign in to post a workaround.
Posted by paulK193 on 3/10/2010 at 2:50 AM
I had exactly the same issue.
My workaround was instead of trying to convert the string to a date convert the date to a string!
As your datetime strings are stored as 'YYYY-MM-DD HH:MM:SS.mmm' then will still be able to inequality test:

CREATE TABLE Test ( TestColumn VARCHAR(256) NOT NULL );
INSERT INTO Test VALUES('20100309');
INSERT INTO Test VALUES('20100310');
INSERT INTO Test VALUES('20100311');
INSERT INTO Test VALUES('This is a test');

DECLARE @VD DATETIME
SET @VD = '10 Mar 2010'
SELECT TestColumn
FROM Test
WHERE (TestColumn < CONVERT(CHAR(8), @VD, 112) )

SET @VD = '11 Mar 2010'
SELECT TestColumn
FROM Test
WHERE (TestColumn < CONVERT(CHAR(8), @VD, 112) )