Databases
StackOverflow2013
Postsdbo
POStrange issue when converting VarChar(max) to DATE or DATETIME in SQL Server 2008
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange issue when converting VarChar(max) to DATE or DATETIME in SQL Server 2008
    text
    copied!<pre><code>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) &gt; DATEADD(DAY, - 90, GETDATE())) ORDER BY DateUpdated DESC </code></pre> <p>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. </p> <p>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.</p> <p>Here is an example of a query that works.</p> <pre><code>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) &gt; 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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload