title: I still get a "Arithmetic overflow" when I filter on a cast datetime even if I use IsDate()
body: <p>I have a data set that I need to filter a date that is stored as a string (changing the source column to a DateTime is NOT a option, this data is coming from a 3rd party source that I can not control).</p>
  
  <p>One of the dates is malformed so if I do the following query I get one result</p>
  
  <pre><code>select ClientID, StartDate from boarding_appts where isdate(StartDate) = 0
  
  ClientID   StartDate
  ---------- --------------------
  5160       5/6/210 12:00:00
  </code></pre>
  
  <p>If I do a <code>cast(StartDate as datetime)</code> I get "<em>Arithmetic overflow error converting expression to data type datetime.</em>", which I expected. and if I filter by <code>IsDate</code> alone everything works fine</p>
  
  <pre><code>select ClientID, cast(StartDate as dateTime) as StartDateCast, datediff(year, cast(StartDate as dateTime), getdate()) as age  from boarding_appts where isdate(StartDate) = 1
  
  ClientID   StartDate               age
  ---------- ----------------------- ----------
  10207      2012-06-09 12:00:00.000 1
  2843       2012-06-23 12:00:00.000 1
  2843       2012-06-23 12:00:00.000 1
  8292       2012-05-11 12:00:00.000 1
  7935       2012-04-24 12:00:00.000 1
  ... (1000's of more rows) ...
  </code></pre>
  
  <h1>Here is my problem:</h1>
  
  <p>I want to filter out records so only records a year old or newer show up, however no-matter how I attempt to perform the filter every one of these queries give me an arithmetic overflow error.</p>
  
  <pre><code>select ClientID, cast(StartDate as dateTime) as StartDateCast, datediff(year, cast(StartDate as dateTime), getdate()) as age  
  from boarding_appts 
  where isdate(StartDate) = 1 
      and datediff(year, cast(StartDate as dateTime), getdate()) &lt; 1 --If you comment out this line it works fine
  
  select * 
  from (select ClientID, cast(StartDate as dateTime) as StartDateCast, datediff(year, cast(StartDate as dateTime), getdate()) as age  from boarding_appts where isdate(StartDate) = 1) as Filtered
  where age &lt; 1 --If you comment out this line it works fine
  
  select * 
  from (select ClientID, cast(StartDate as dateTime) as StartDateCast  from boarding_appts where isdate(StartDate) = 1) as Filtered
  where datediff(year, StartDateCast, getdate()) &lt; 1 --If you comment out this line it works fine
  
  ;with Filtered as
  (select ClientID, cast(StartDate as dateTime) as StartDateCast  from boarding_appts where isdate(StartDate) = 1)
  select * from Filtered
  where datediff(year, StartDateCast, getdate()) &lt; 1 --If you comment out this line it works fine
  
  ;with Filtered as
  (select ClientID, cast(StartDate as dateTime) as StartDateCast, datediff(year, cast(StartDate as dateTime), getdate()) as age  from boarding_appts where isdate(StartDate) = 1)
  select * from Filtered
  where age &lt; 1 --If you comment out this line it works fine
  </code></pre>
  
  <p>Here is a <a href="http://sqlfiddle.com/#!3/3cb56/6" rel="nofollow">test set of data on SQL Fiddle</a> for you to try out any solutions on. I am out of ideas on how to fix this. The ONLY solution I could think of that worked was selecting in to a temporary table first then selecting it out</p>
  
  <pre><code>select ClientID, StartDate, cast(StartDate as dateTime) as StartDateCast, datediff(year, cast(StartDate as dateTime), getdate()) as age  
  into #t
  from boarding_appts 
  where isdate(StartDate) = 1 
  
  select * from #t where age &lt; 1 --Works.
  </code></pre>