0

I know there are numerous questions about this topic, even one I asked myself a while ago (here). Now I ran into a different problem, and neither myself nor my colleagues know what the reason for the strange behaviour is.

We've got a relatively simple SQL statement quite like this:

SELECT
    CONVERT(DATETIME, SUBSTRING(MyText, CHARINDEX('Date:', MyText) + 8, 16) AS MyDate,
    SomeOtherColumn,
    ...
FROM
    MyTable
INNER JOIN MyOtherTable
    ON MyTable.ID = MyOtherTable.MyTableID
WHERE
    MyTable.ID > SomeValue AND
    MyText LIKE 'Date: %'

This is not my database and also not my SQL statement, and I didn't create the great schema to store datetime values in varchar columns, so please ignore that bit.

The problem we are facing right now is a SQL conversion error 241 ("Conversion failed when converting date and/or time from character string.").

Now I know that the query optimiser may change the execution plan that the WHERE clause may be used to filter results after the conversion is attempted, but the really strange thing is that I don't get any errors when I delete all of the WHERE clause.

I also don't get any errors when I add a single line to the statement above as follows:

SELECT
    MyText, -- This is the added line
    CONVERT(DATETIME, SUBSTRING(MyText, CHARINDEX('Date:', MyText) + 8, 16) AS MyDate,
    ...

As soon as I remove it I get the conversion error again. Manually checking the values in the MyText column without trying to convert them does not show that there are any records which might cause a problem.

What is the reason for the conversion error? Why do I not run into it when I also select the column as part of the SELECT statement?

Update

Here the execution plan, although I don't think it's going to help. part1 enter image description here

Community
  • 1
  • 1
Gorgsenegger
  • 7,356
  • 4
  • 51
  • 89
  • What is the value of the `myText` column of `first row` when you run the query `without where` clause? – Kaf Mar 05 '13 at 11:05
  • Why `CHARINDEX('Date:', MyText)` when your `LIKE` clause guarantees that `MyText` *starts* with `Date:`? – Damien_The_Unbeliever Mar 05 '13 at 11:09
  • @Kaf: Data is similar to: Ping does not reach 11.22.33.44: 11.22.33.44 is 0 percent reachable 1. attempt Date: 31.12.2000 12:34:56 IP: 11.22.33.44 It is not my stuff that generates this. – Gorgsenegger Mar 05 '13 at 12:13
  • @Damien: I know, but this how it was "developed", but it doesn't matter anyway in regard to the conversion error. – Gorgsenegger Mar 05 '13 at 12:14
  • I think it is your data. It would be easy to confirm if you could give us a [fiddle](http://sqlfiddle.com/#!3) which could run without where and fail with where. – Kaf Mar 05 '13 at 12:33
  • I would provide data to reproduce the problem if I could. Unfortunately I'm not able to generate a simple example where the same problem occurs. The actual tables consist of many thousand rows, and looking through the sorted column with the date doesn't reveal any problems. I also saved the output of the statement without the `WHERE` clause and `DateTime.TryParse`d line by line in a .NET console application, no problems. We're pretty clueless as we've never seen such a behaviour - would it be possible that something in the table/database structure is corrupt? – Gorgsenegger Mar 05 '13 at 14:40

1 Answers1

1

Sometimes, SQL Server aggressively optimizes by pushing conversion operations earlier in the process than they would otherwise need to be. (It shouldn't. See SQL Server should not raise illogical errors on Connect, as an example).

When you just select:

CONVERT(DATETIME, SUBSTRING(MyText, CHARINDEX('Date:', MyText) + 8, 16)

Then the optimizer decides it can perform this conversion as part of the table/index scan or seek - right at the point at which it's reading the data from the table (and, importantly, before, or at the same time, as the WHERE clause filter). The rest of the query can then just use the converted value.

When you select:

MyText, -- This is the added line
CONVERT(DATETIME, SUBSTRING(MyText, CHARINDEX('Date:', MyText) + 8, 16)

It decides to let the conversion happen later. Importantly, the conversion now (by happenstance) happens later than the WHERE clause filter which should, by rights, be filtering all rows before the conversion is attempted.

The only safe way to deal with this is to force the filtering to definitely occur before the conversion is attempted. If you're not dealing with aggregates, a CASE expression may be safe enough:

SELECT CASE WHEN MyText LIKE 'Date: %' THEN CONVERT(DATETIME, SUBSTRING(MyText, CHARINDEX('Date:', MyText) + 8, 16) END

Otherwise, the even safer option is to split the query into two separate queries, and store the intermediate results in a temp table or table variable (views, CTEs and subqueries don't count, because the optimizer can "see through" such constructs)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thanks, this is what is described in the answer of my other question I linked to above. But when I do not use a WHERE clause at all, then I **don't** get the error. This is very strange, how can I have seemingly less data when I omit the WHERE clause completely compare to whichever WHERE clause? – Gorgsenegger Mar 05 '13 at 11:06
  • Generate the query plans and edit them into your question for a more detailed look into it. – Damien_The_Unbeliever Mar 05 '13 at 11:12
  • I will update the question tonight as I cannot upload the plan screenshots due to WTS internet restrictions. – Gorgsenegger Mar 05 '13 at 12:21
  • I uploaded the image for the working execution plan, the query causing the error does not generate one. – Gorgsenegger Mar 05 '13 at 20:46
  • The first version was too small - thought you could somehow see the original. I uploaded it in two parts now. – Gorgsenegger Mar 06 '13 at 17:34