title: Converted date format inconsistent after loading into destination table
body: <p>I am using a merge statement to load data where I have to calculate and convert a date field and load into a varchar column in the dest table</p>
  
  <pre><code>MERGE dbo.DestTable AS Target
      USING (
      SELECT ID, 
      MAX(CAST(CONVERT(date,dateadd(day, CAST(date as int),'1960-01-01'))AS VARCHAR(30))) date,
      max(col1) col1,
      max(col2) col2
      FROM dbo.Staging
      GROUP BY  ID
      )
      AS Source
      ON ( Target.ID = Source.ID )
      WHEN MATCHED THEN 
          UPDATE SET  Target.Date = Source.date,
                      Target.col1= Source.col1,
                      Target.col2= Source.col2,
  
      WHEN NOT MATCHED BY TARGET THEN 
          INSERT (ID
                 ,Date
                 ,col1
                 ,col2
                 )
          VALUES (
                      Source.ID,
                      Source.date,
                      Source.col1,
                      Source.col2
                      );
  </code></pre>
  
  <p>Just running the select statement gives me the second output and querying the destination table gives the first output. The destination column is of the same datatype as the final converted source field.</p>
  
  <p>Output is in the link below</p>
  
  <p><a href="https://i.stack.imgur.com/WbZTE.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WbZTE.jpg" alt="date format inconsistency"></a></p>
  
  <p>I have tried scouring the net for a solution as well but haven't found much to help. I would really appreciate it if someone could tell me where I'm going wrong. Thanks in advance!</p>