title: Validate Date Values in NVarchar field
body: <p>Hi I have a reporting application written against some 3rd party software.  Unfortunately it stores all values as nvarchar and does not validate data entry on the client side as a result I am getting the following error when</p>
  
  <pre><code>"Conversion failed when converting date and/or time from character string"  
  System.Data.SqlClient.SqlException was unhandled by user code
  </code></pre>
  
  <p>or if I try to execute the code in SSMS:</p>
  
  <pre><code>Msg 241, Level 16, State 1, Procedure settlement_list, Line 10
  Conversion failed when converting date and/or time from character string.
  </code></pre>
  
  <p>I assume this is the result of someone entering a text value in the data field so I've tried this ISDATE code to find the bad value:</p>
  
  <pre><code>   SELECT mat3_02_01, CONVERT(datetime, mat3_04_02), mat3_04_02 FROM lntmu11.matter3
     WHERE ISDATE(mat3_04_02) &lt;&gt; 1
     AND Coalesce(mat3_04_02, '') &lt;&gt; ''
     order by mat3_04_02 desc
  </code></pre>
  
  <p>and I get zero row returned ... I also manually sifted through the data (its sveral 100 thousand rows so its kind of hard and see no bad values ??? </p>
  
  <p>Does anyone have any suggestions ?</p>
  
  <p>EDIT ---</p>
  
  <p>Here is the stored proc (I know where clause is ugly)</p>
  
  <pre><code>SELECT mat_no, 'index'=matter.mat1_01_06,
    'insurance'=Replace(Replace(matter.mat1_03_01, 'INSURANCE COMPANY', ' '), 'COMPANY', ''), 
    matter.[status], 'casestage'=mat1_04_01, 'injured'=matter.MAT1_01_07, matter.client,
   'terms'=mat3_04_06, 'ClmAmt'=matter.mat1_07_01, 
    'ClmBal'=matter.mat1_07_03, 'SetTot'=matter3.MAT3_04_09, 'By'=mat3_03_02,
    'DtSttld'=mat3_04_02, 'SettlStg'=(MAT3_06_08 + ' / ' + MAT3_06_05)
  FROM [lntmu11].matter3 inner join 
  [lntmu11].matter ON [lntmu11].matter.sysid = [lntmu11].matter3.sysid
  WHERE
  (DateDiff(month, convert(datetime, MAT3_04_02, 101), GETDATE()) = @range 
    and mat3_03_02 like @by)
  or
  (mat3_04_06 like @by2 
    and DateDiff(month, convert(datetime, MAT3_04_02, 101),  GETDATE()) = @range) 
  ORDER BY MAT3_03_02
  </code></pre>