Conversion failure with date when doing a count of result

Question

I have been hitting a strange issue lately on SQL Server.

The application I am using is saving user's attributes in a table containing user/attribute/value records. I need to find whether a user is still valid using his end of validity date. The query I have been using is the following:

SELECT COUNT(*) FROM value_all
WHERE AttrName = 'VALIDTO' AND
    userkey=574924 AND
    CONVERT (DATE,Value, 120) <  GETDATE();

The conversion is necessary because all values are stored as VARCHAR.

The query was working correctly but lately I have been hitting the following error:

SQL Error [241] [S0001]: Conversion failed when converting date and/or time from character string.

I though that the stored validity date was wrong. However, the value does match the correct format:

SELECT * FROM value_all 
WHERE AttrName = 'VALIDTO' AND
    userkey=574924 AND

|userkey |AttrName   |Value               |
|--------|-----------|--------------------|
|574924  |VALIDTO    |2016-07-31T23:59:59 |

I can even do a convert and show the result correctly:

SELECT CONVERT (DATE,Value, 120) AS Date FROM value_all
WHERE AttrName = 'VALIDTO' AND
    userkey=574924 AND
    CONVERT (DATE,Value, 120) <  GETDATE();

|Date       |
|-----------|
|2016-07-31 |

However, as soon as I add the COUNT, the error is coming back.

Does any-one have an idea of what is going on in this case? Is it linked, in some way, to the value used?

EDIT

Thanks to the question at "Conversion to datetime fails only on WHERE clause?", I was able to rework my query as follow, which correct the issues I had:

SELECT COUNT(*) FROM value_all
WHERE AttrName = 'VALIDTO' AND
    userkey=574924 AND
    CONVERT (DATE, CASE WHEN ISDATE(Value) THEN Value END, 120) <  GETDATE();

Instead of using `COUNT(*) ` Use `COUNT(CONVERT (DATE,Value, 120)) ` — artm, Sep 15 '16 at 13:14
have you tried select count(1) in stead of select count(*) ? It probably wont make a difference but just to be sure — GuidoG, Sep 15 '16 at 13:14
I'd like to point you to this answer, especially to the link at the bottom: http://stackoverflow.com/a/39504506/5089204 — Shnugo, Sep 15 '16 at 13:15
@artm, it does not change the result, I still have the error when using the convert in the COUNT — Jupotter, Sep 15 '16 at 13:32
@Shnugo, thanks for the link, I will try to look at it. However, I do not see the similarity with the proposed duplicate beside the error message. Could you maybe point to a more precise part of this question? — Jupotter, Sep 15 '16 at 13:36
@Jupotter, the next link about *fails only in WHERE clause* is very related to the one I posted, but closer to the point. It's a well known issue, that - due to unexpected order of execution - you run into conversion errors where you think this is impossible... — Shnugo, Sep 15 '16 at 13:41
Basically, the table has values that can't be converted to `date`. These values are not `WHERE AttrName = 'VALIDTO' AND userkey=574924`, but the server still reads these rows and tries to convert the values and fails. You can verify it by `SELECT CONVERT (DATE,Value, 120) AS Date FROM value_all`, i.e. `SELECT` without `WHERE`. — Vladimir Baranov, Sep 15 '16 at 13:41

score 1 · Answer 1 · answered Sep 15 '16 at 13:24

1

The answer is that there is no guarantee of the order in which the WHERE clause operates. It looks like it may be applying the Convert of the value field before it filters the rows on userkey or attrName. Most likely the count is causing the plan to change which is just an unlucky side effect.

You chould retrieve the row with the userkey and attrname and then convert the value.

Curious, is there an index on the userkey field? And on attrname?

answered Sep 15 '16 at 13:24

paulbarbin

382
2
9

Also curious to know if the plan is showing the order of predicates (I don't know if the order of predicates is stated in the plan, but I'm wondering. Can you post it at https://www.brentozar.com/pastetheplan/ – paulbarbin Sep 15 '16 at 13:27
Hi. You were right to look at indexing, both UserKey, AttrName and Value are indexed, however doing the count on a non-indexed column does not cause the issue. I guess it is also a different plan, I will try to post them. – Jupotter Sep 15 '16 at 13:29
1

also, the answer is better given here http://stackoverflow.com/questions/7263501/conversion-to-datetime-fails-only-on-where-clause – paulbarbin Sep 15 '16 at 13:32
Yes, that seems to be the real issue. I will have to use the "ISDATE" solution proposed there, as I cannot change the schema of the database – Jupotter Sep 15 '16 at 13:39

Conversion failure with date when doing a count of result

EDIT

1 Answers1