title: SQL Server Type Cast for DateTime field and UNION ALL
body: <p>I am facing a strange behaviour of SQL Server (T-SQL) when it comes to using <code>UNION ALL</code> and having to pad one table (the one to be joined by the <code>UNION</code> command).</p>
  
  <p>Table 1:</p>
  
  <pre><code>Firstname  Lastname  Date
  ---------  --------  ----
  Peter      Pan       2013-01-21 12:29:55.000
  Paul       Am        2013-01-22 12:39:45.000
  </code></pre>
  
  <p>and Table 2:</p>
  
  <pre><code>FN         LN
  ---------  --------
  Laura      Loeb
  Lisa       Lefteye
  </code></pre>
  
  <p>I want to do a <code>UNION ALL</code> to both tables. Which can only be achieved by "padding" Table 2 with a "fake" third column (since it has no "data" column), like so:</p>
  
  <pre><code>SELECT
    Firstname AS FName, Lastname AS LName, Date AS TheDate
  FROM Table1
  
  UNION ALL
  
  SELECT
    FN AS FName, LN AS LName, NULL AS TheDate
  FROM Table2
  </code></pre>
  
  <p>My problem is: SQL Server gives me an error message telling me that it cannot cast to <code>DateTime</code> type.</p>
  
  <p>I am 100% sure that my datatype for column <code>Date</code> in Table1 is set to <code>DateTime</code>, NULL values are allowed, too.</p>
  
  <p>I have tried setting a dummy date, like so:</p>
  
  <pre><code>... FN AS FName, LN AS LName, '2013-03-08' AS TheDate ...
  </code></pre>
  
  <p>I have tried setting the current date, like so:</p>
  
  <pre><code>... FN AS FName, LN AS LName, getdate() AS TheDate ...
  </code></pre>
  
  <p>None of the above work.</p>
  
  <p>However, I know that the solution in general (padding missing columns by using "NULL AS Columnname") works...</p>
  
  <p>Does anybody know how to solve that problem?</p>
  
  <p>Thank you very much in advance!</p>