title: Merge with datetime compare in sqlserver
body: <p>I am having a temporary table (Table T) and another table having final data (tale F). </p>
  
  <p>I need to compare a key field (tin here) and if record already exists and record creation date is older than the one in T, </p>
  
  <p>I want to update the record in F. </p>
  
  <p>If the record is not available in <code>T</code> i want to replace it. </p>
  
  <p>I have used merge as given below -</p>
  
  <pre><code>MERGE Digitization329 AS NF
  USING 
      ( SELECT    *
        FROM      TMP_Npr
      ) AS NT
  ON NF.Tin = NT.Tin
  WHEN MATCHED AND NF.[RCRD_CRN_DATE] &lt; NT.[RCRD_CRN_DATE]
      THEN 
          UPDATE
        SET       [FULLNAME] = NT.FULLNAME ,
                  [FATHERNAME] = NT.FATHERNAME ,
                  [MOTHERNAME] = NT.MOTHERNAME ,
                  [SPOUSENAME] = NT.SPOUSENAME
  WHEN NOT MATCHED 
      THEN 
          INSERT  (
                    [TIN] ,
                    [FTIN] ,
                    [FULLNAME] ,
                    [FATHERNAME] ,
                    [MOTHERNAME] ,
                    [SPOUSENAME]
                  )
        VALUES    ( NT.TIN ,
                    NT.FTIN ,
                    NT.FULLNAME ,
                    NT.FATHERNAME ,
                    NT.MOTHERNAME ,
                    NT.SPOUSENAME
                  );
  </code></pre>
  
  <p><code>RCRD_CRN_DATE</code> in both the tables are of <code>datetime</code> type. </p>
  
  <p>However i always get duplicate records in the F Table. </p>
  
  <p>I am  unable to find out the reason why WHEN MATCHED AND NF.[RCRD_CRN_DATE] &lt; NT.[RCRD_CRN_DATE]  is not working.</p>
  
  <p>Thanks. </p>
  
  <p>My T table contains three records</p>
  
  <pre><code>  tin RCRD_CRN_DATE ...
    123 10-11-2013-12.20.30
    123 10-11-2013-12.20.35
    345 10-11-2013-01.10.10
  </code></pre>
  
  <p>After the operation F table has all three records.</p>