<p>I have a large table with 1 million+ records. Unfortunately, the person who created the table decided to put dates in a <code>varchar(50)</code> field.</p>

<p>I need to do a simple date comparison -</p>

<pre><code>datediff(dd, convert(datetime, lastUpdate, 100), getDate()) &lt; 31
</code></pre>

<p>But it fails on the <code>convert()</code>:</p>

<pre><code>Conversion failed when converting datetime from character string.
</code></pre>

<p>Apparently there is something in that field it doesn't like, and since there are so many records, I can't tell just by looking at it. How can I properly sanitize the entire date field so it does not fail on the <code>convert()</code>? Here is what I have now:</p>

<pre><code>select count(*)
from MyTable
where
    isdate(lastUpdate) &gt; 0
    and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) &lt; 31
</code></pre>

<hr>

<p><a href="https://stackoverflow.com/questions/28110/ms-sql-datetime-conversion-failure#28209">@SQLMenace</a></p>

<p>I'm not concerned about performance in this case. This is going to be a one time query. Changing the table to a datetime field is not an option.</p>

<p><a href="https://stackoverflow.com/questions/28110/ms-sql-datetime-conversion-failure#28139">@Jon Limjap</a></p>

<p>I've tried adding the third argument, and it makes no difference.</p>

<hr>

<p><a href="https://stackoverflow.com/questions/28110/ms-sql-datetime-conversion-failure#28209">@SQLMenace</a></p>

<blockquote>
  <p>The problem is most likely how the data is stored, there are only two safe formats; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh:mm:ss:mmm (no spaces)</p>
</blockquote>

<p>Wouldn't the <code>isdate()</code> check take care of this?</p>

<p>I don't have a need for 100% accuracy. I just want to get most of the records that are from the last 30 days.</p>

<hr>

<p><a href="https://stackoverflow.com/questions/28110/ms-sql-datetime-conversion-failure#28209">@SQLMenace</a></p>

<pre><code>select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0
</code></pre>

<hr>

<p><a href="https://stackoverflow.com/questions/28110/ms-sql-datetime-conversion-failure#28209">@Brian Schkerke</a></p>

<blockquote>
  <p>Place the CASE and ISDATE inside the CONVERT() function.</p>
</blockquote>

<p>Thanks! That did it.</p>

[{'answer_id': 28135, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': '<p>I would suggest cleaning up the mess and changing the column to a datetime because doing stuff like this</p>\n\n<pre><code>WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) &lt; 31\n</code></pre>\n\n<p>cannot use an index and it will be many times slower than if you had a datetime colum,n and did</p>\n\n<pre><code>where lastUpdate &gt; getDate() -31\n</code></pre>\n\n<p>You also need to take into account hours and seconds of course</p>\n'}, {'answer_id': 28139, 'author': 'Jon Limjap', 'author_id': 372, 'author_profile': 'https://Stackoverflow.com/users/372', 'pm_score': 0, 'selected': False, 'text': '<p>In your convert call, you need to specify a third style parameter, e.g., the format of the datetimes that are stored as varchar, as specified in this document: <a href="http://doc.ddart.net/mssql/sql70/ca-co_1.htm" rel="nofollow noreferrer">CAST and CONVERT (T-SQL)</a></p>\n'}, {'answer_id': 28146, 'author': 'Ian Nelson', 'author_id': 2084, 'author_profile': 'https://Stackoverflow.com/users/2084', 'pm_score': 0, 'selected': False, 'text': '<p>Print out the records. Give the hardcopy to the idiot who decided to use a varchar(50) and ask them to find the problem record.</p>\n\n<p>Next time they might just see the point of choosing an appropriate data type.</p>\n'}, {'answer_id': 28162, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': '<p>The problem is most likely how the data is stored, there are only two safe formats</p>\n\n<p>ISO YYYYMMDD</p>\n\n<p>ISO 8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)</p>\n\n<p>these will work no matter what your language is.</p>\n\n<p>You might need to do a SET DATEFORMAT YMD (or whatever the data is stored as) to make it work</p>\n'}, {'answer_id': 28176, 'author': 'Ian Nelson', 'author_id': 2084, 'author_profile': 'https://Stackoverflow.com/users/2084', 'pm_score': 1, 'selected': False, 'text': "<p>How about writing a cursor to loop through the contents, attempting the cast for each entry?</p>\n\n<p>When an error occurs, output the primary key or other identifying details for the problem record.</p>\n\n<p>I can't think of a set-based way to do this.</p>\n\n<p><strong>Edit</strong> - ah yes, I forgot about ISDATE(). Definitely a better approach than using a cursor. +1 to SQLMenace.</p>\n"}, {'answer_id': 28184, 'author': 'Skerkles', 'author_id': 3067, 'author_profile': 'https://Stackoverflow.com/users/3067', 'pm_score': 4, 'selected': True, 'text': '<p>Place the <code>CASE</code> and <code>ISDATE</code> inside the <code>CONVERT()</code> function.</p>\n\n<pre class="lang-sql prettyprint-override"><code>SELECT COUNT(*) FROM MyTable\nWHERE\n    DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate)\n        WHEN 1 THEN lastUpdate\n        ELSE \'12-30-1899\'\n    END), GetDate()) &lt; 31\n</code></pre>\n\n<p>Replace <code>\'12-30-1899\'</code> with the default date of your choice.</p>\n'}, {'answer_id': 28186, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': "<blockquote>\n  <p>Wouldn't the isdate() check take care of this?</p>\n</blockquote>\n\n<p>Run this to see what happens</p>\n\n<pre><code>select isdate('20080131')\nselect isdate('01312008')\n</code></pre>\n"}, {'answer_id': 28206, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': "<blockquote>\n  <p>How about writing a cursor to loop through the contents, attempting the cast for each entry?When an error occurs, output the primary key or other identifying details for the problem record.\n  I can't think of a set-based way to do this.</p>\n</blockquote>\n\n<p>Not totally setbased but if only 3 rows out of 1 million are bad it will save you a lot of time</p>\n\n<pre><code>select * into BadDates\nfrom Yourtable\nwhere isdate(lastUpdate) = 0\n\nselect * into GoodDates\nfrom Yourtable\nwhere isdate(lastUpdate) = 1\n</code></pre>\n\n<p>then just look at the BadDates table and fix that</p>\n"}, {'answer_id': 28209, 'author': 'Skerkles', 'author_id': 3067, 'author_profile': 'https://Stackoverflow.com/users/3067', 'pm_score': 2, 'selected': False, 'text': "<p>The ISDATE() would take care of the rows which were not formatted properly if it were indeed being executed first.  However, if you look at the execution plan you'll probably find that the DATEDIFF predicate is being applied first - thus the cause of your pain.</p>\n\n<p>If you're using SQL Server Management Studio hit <kbd>CTRL</kbd>+<kbd>L</kbd> to view the estimated execution plan for a particular query.</p>\n\n<p>Remember, SQL isn't a procedural language and short circuiting logic may work, but only if you're careful in how you apply it.</p>\n"}, {'answer_id': 646058, 'author': 'chris raethke', 'author_id': 43672, 'author_profile': 'https://Stackoverflow.com/users/43672', 'pm_score': 0, 'selected': False, 'text': '<p>I am sure that changing the table/column might not be an option due to any legacy system requirements, but have you thought about creating a view which has the date conversion logic built in, if you are using a more recent version of sql, then you can possibly even use an indexed view?</p>\n'}]
['https://Stackoverflow.com/questions/28110', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/357/']