title: SQl Server Converting to Date fails , DateTime works
body: <p>I have a table with a <code>varchar(25)</code> column that holds a date value. A typical value is '11/04/2017'.  </p>
  
  <p>This query returns 0 rows </p>
  
  <pre><code>select * 
  from myTable  
  where isdate(inputDate) = 0
  </code></pre>
  
  <p>I am trying to find a max on this, using a date sort. </p>
  
  <p>This query returns the expected result</p>
  
  <pre><code>;with gooddates as 
  (
      select 
          medcomfolder, PatientId, PatientBirthday, InputDate 
      from 
          myTable 
      where 
          isdate(inputDate) = 1
  )
  select max(convert(datetime, inputDate)) 
  from gooddates
  </code></pre>
  
  <p>This query returns an error. </p>
  
  <pre><code>;with gooddates as 
  (
       select 
           medcomfolder, PatientId, PatientBirthday, InputDate 
       from 
           dwhFuData   
       where 
           isdate(inputdate) = 1
  )
  select max(convert(date, inputdate)) 
  from gooddates
  </code></pre>
  
  <p>This is the returned error </p>
  
  <blockquote>
    <p>Msg 241, Level 16, State 1, Line 274<br>
    Conversion failed when converting date and/or time from character string</p>
  </blockquote>
  
  <p>The difference between the 2 queries is that the first is converting to a dateTime while the latter is converting to a date. </p>
  
  <p>At this point, I can move forward w/ the dateTime option, but I am left wondering what I am missing.  </p>
  
  <p>I have checked that there are no embedded spaces, and all the columns have a  <code>len(InputDate) = 10</code>  (there is NO time data included)</p>
  
  <p>I selected distinct values,put them in excel, and did a date function on each row. I was hoping to get a <strong>#VALUE</strong> on 1 row. All the rows worked. </p>
  
  <p>So there is nothing silly like '02/31/2019' going on. </p>
  
  <p>How can a dateTime conversion pass when a simple date conversion does not?</p>