title: how to compare the dates in different format with the getdate() in sql server
body: <p>I have a column, where the date values are stored in <code>nvarchar</code> data type.
  The date format is <code>dd-mm-yy, dd.mm.yy</code></p>
  
  <p>Currently I am replacing the <code>-</code> with <code>.</code> and comparing with get date, as all dates formats used in other tables are in <code>dd.mm.yyyy</code> format.</p>
  
  <p>Now consider a scenario where the dates may be in other formats like <code>dd/mm/yyyy</code> or other date formats, is there any way to check whether the date is valid and compare it with <code>getdate()</code></p>
  
  <p>Also I find few unknown records are also available like 57937234, I need to ignore them.</p>
  
  <p>As of now I am using this query and it is working fine:</p>
  
  <pre><code>select 
      A.Value, replace( B.Value,'-','.') 
  from 
      smpltbl as A
  INNER JOIN 
      smpltbl as B ON A.MSKEY = B.MSKEY
  INNER JOIN 
      smpltbl as tbl_status ON A.MSKEY = tbl_status.MSKEY
  where 
      A.colname = 'XYZ' 
      AND B.colname = 'ABC' 
      AND tbl_status.colname = 'Status' 
      AND tbl_status.Value = 'OK'
      AND B.Value &lt;&gt; '00.00.0000' 
      and B.Value &lt;&gt;'..' 
      AND replace( B.Value,'-','.') &lt; cast(convert(nvarchar, GETDATE(), 104)
  </code></pre>
  
  <p>Can someone kindly help me in accepting the dates in different format an comparing with the system date and the result must be displayed in <code>DD.MM.YYYY</code> format itself</p>