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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 498804 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/20/2009 9:44:14 AM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Jim [MSFT] on 11/4/2009 at 10:45 AM
Seems like no further discussion on this one, so closing as "by-design".


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


Posted by Jim [MSFT] on 10/27/2009 at 2:00 PM

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.


Jim Hogg