Strange issue when converting VarChar(max) to DATE or DATETIME in SQL Server 2008

Question

SELECT     Files.URL, Files.MD5, Files.Thumbnail, Files.Title, CONVERT(DATE, AttributeData.Keyword) AS DateUpdated, Attributes.Name AS DateType, 
                      Metadata.metadata
FROM         Files INNER JOIN
                      Metadata ON Files.ID = Metadata.FileID INNER JOIN
                      FilesToAttributeData ON Files.ID = FilesToAttributeData.FileID INNER JOIN
                      AttributeData ON FilesToAttributeData.AttributeDataID = AttributeData.ID INNER JOIN
                      Attributes ON AttributeData.AttributeID = Attributes.ID
WHERE       (Files.GeneralSearch = 1) AND 
            (Attributes.Name = 'Process Date' OR Attributes.Name = 'Publish Date') AND 
            (ISDATE(AttributeData.Keyword) = 1) AND 
            (CONVERT(DATE, AttributeData.Keyword) > DATEADD(DAY, - 90, GETDATE()))
ORDER BY DateUpdated DESC

This is my original sql query. It seems to work fine with our data in our dev environment. In production however I get the following error. "Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.". Now if I remove the convert function in the SELECT and just output AttributeData.Keyword it will work fine. If I leave that alone and remove the convert function in the where clause it will work fine. It will not work if they both exist though.

Any ideas what could cause this? I have tried CAST and I have tried using a specific date style. Our dates generally look like yyyymmdd. An example is '20110318'. If I replace AttributeData.Keyword with this string it will also fail. I really have no idea what is going on here.

Here is an example of a query that works.

SELECT     (AttributeData.Keyword) AS DateUpdated, Attributes.Name AS DateType

    FROM         Files INNER JOIN
                          Metadata ON Files.ID = Metadata.FileID INNER JOIN
                          FilesToAttributeData ON Files.ID = FilesToAttributeData.FileID INNER JOIN
                          AttributeData ON FilesToAttributeData.AttributeDataID = AttributeData.ID INNER JOIN
                          Attributes ON AttributeData.AttributeID = Attributes.ID
    WHERE       (Files.GeneralSearch = 1) AND 
                (Attributes.Name = 'Process Date' OR Attributes.Name = 'Publish Date') AND 
                (ISDATE(AttributeData.Keyword) = 1) AND 
                (CONVERT(DATE, AttributeData.Keyword) > DATEADD(DAY, - 90, GETDATE()))
    ORDER BY DateUpdated DESC

    DateUpdated DateType
    20110318    Process Date
    20110318    Process Date
    20110315    Process Date
    20110315    Process Date
    20110303    Process Date
    20110303    Publish Date
    20110302    Process Date
    20110301    Process Date
    20110301    Publish Date
    20110225    Process Date
    20110223    Process Date
    20110201    Publish Date
    20110201    Process Date
    20110127    Process Date
    20110118    Publish Date
    20110101    Publish Date
    20110101    Publish Date
    20101231    Process Date
    20101231    Publish Date

So are you saying that `select CONVERT(DATE, '20110318')` causes an error on that server? If not you are probably hitting this issue http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0/5203211#5203211 — Martin Smith, Mar 22 '11 at 00:00
@Martin - Yes this is causing an error as well. It is very strange because it only causes the error if the other convert is in the where clause. It seems I can use either convert but not both at the same time even though they are using the same column. — MurderDev, Mar 22 '11 at 00:09
No. If you run that query **on its own** do you get an error? `select CONVERT(DATE, '20110318')` I suspect that the answer is no in which case you'll probably find that the issue is that it is doing the convert for rows not meeting the `ISDATE` predicate. No particular order of evaluation is guaranteed unless you use `CASE` — Martin Smith, Mar 22 '11 at 00:12
@Martin - Oh no I do not. I bet what you linked to is what is happening. The optimizer may be causing it so that things are evaluated differently (out of order) than I expect. Hmm what would be the best way to handle this? I expect the ISDATE to be evaluated before the convert but that may not be happening. If I remove either convert then they happen as expected but that doesn't mean they have to. — MurderDev, Mar 22 '11 at 00:13

score 3 · Accepted Answer · answered Mar 22 '11 at 00:17

In SQL Server no particular order of evaluation is guaranteed except for CASE statements so it may well be doing the CONVERT(DATE, AttributeData.Keyword) before the ISDATE(AttributeData.Keyword) = 1 filter (you can confirm this by looking at the execution plans).

To get around this you can replace CONVERT(DATE, AttributeData.Keyword) with

     CASE
           WHEN ISDATE(AttributeData.Keyword) = 1 THEN
           CONVERT(DATE, AttributeData.Keyword)
         END

So can you try

SELECT Files.URL,
       Files.MD5,
       Files.Thumbnail,
       Files.Title,
       CASE
         WHEN ISDATE(AttributeData.Keyword) = 1 THEN
         CONVERT(DATE, AttributeData.Keyword)
       END             AS DateUpdated,
       Attributes.Name AS DateType,
       Metadata.metadata
FROM   Files
       INNER JOIN Metadata
         ON Files.ID = Metadata.FileID
       INNER JOIN FilesToAttributeData
         ON Files.ID = FilesToAttributeData.FileID
       INNER JOIN AttributeData
         ON FilesToAttributeData.AttributeDataID = AttributeData.ID
       INNER JOIN Attributes
         ON AttributeData.AttributeID = Attributes.ID
WHERE  ( Files.GeneralSearch = 1 )
       AND ( Attributes.Name = 'Process Date'
              OR Attributes.Name = 'Publish Date' )
       AND ( CASE
               WHEN ISDATE(AttributeData.Keyword) = 1 THEN
               CONVERT(DATE, AttributeData.Keyword)
             END > DATEADD(DAY, -90, GETDATE()) )
ORDER  BY DateUpdated DESC

This is what I concluded after martins link. I used a temp table and that worked but I like this approach better. Thank you! Edit: I just realized you are martin. :) — MurderDev, Mar 22 '11 at 00:25

score 1 · Answer 2 · answered Mar 22 '11 at 00:18

1

You could also try adding

(ISDATE(AttributeData.Keyword) = 1)

as a join condition on AttributeData, but I think you are still not guaranteed order of execution there.

answered Mar 22 '11 at 00:18

Kimberly

2,657
2
16
24

Ah, I feel the lure of a clean solution, too, but unless you can get a SQL expert to definitively promise that this will enforce an order, you are better with Martin's answer. Even if this works today, QO may later change its mind after your data changes a little. Thanks for the bump, though. – Kimberly Mar 22 '11 at 00:34
I did try this and it does work fine. I am not sure if it is a perfect solution because I don't know how the optimizer works exactly but from what I can imagine it should be. The set should not be evaluated until it joins all data in my opinion but who knows. Thank you! – MurderDev Mar 22 '11 at 00:37
@MurderDev - You're not [the only one to feel that way](https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors#) but currently that's how it works! – Martin Smith Mar 22 '11 at 01:05

score 0 · Answer 3 · answered Mar 22 '11 at 00:21

And this seems to work fine....

    DECLARE @tempFiles TABLE(DateUpdated varchar(MAX))

INSERT INTO @tempFiles(DateUpdated)
SELECT     AttributeData.Keyword AS DateUpdated
FROM         Files INNER JOIN
                      Metadata ON Files.ID = Metadata.FileID INNER JOIN
                      FilesToAttributeData ON Files.ID = FilesToAttributeData.FileID INNER JOIN
                      AttributeData ON FilesToAttributeData.AttributeDataID = AttributeData.ID INNER JOIN
                      Attributes ON AttributeData.AttributeID = Attributes.ID
WHERE       (Files.GeneralSearch = 1) AND 
            (Attributes.Name = 'Process Date' OR Attributes.Name = 'Publish Date') AND 
            (ISDATE(AttributeData.Keyword) = 1) AND 
            (CONVERT(DATETIME, AttributeData.Keyword, 112) > DATEADD(DAY, - 90, GETDATE()))
ORDER BY DateUpdated DESC

SELECT CONVERT(DATE, DateUpdated) FROM @tempFiles

It appears that Martin was correct in that the SQL optimizer is reordering which parts get evaluated first which means converts are happening before ISDATE so I am actually processing invalid dates. ATM the only way I know to fix this is to use a temp table and evaluate without the convert in the select statement until I am ready to return the results...

Strange issue when converting VarChar(max) to DATE or DATETIME in SQL Server 2008

3 Answers3