title: Getting 'conversion failed...' when using UNION ALL, but running the queries separately works
body: <p>I am trying to use <code>SELECT INTO</code> and <code>UNION ALL</code> to join 3 tables together.</p>
  
  <p>Table 1 uses a number of <code>Ltrim(Rtrim())</code> and <code>CONVERT</code> commands, whilst with the other two tables, I am just taking their columns out and pasting them into this combined table.</p>
  
  <p>When I try to run the query, I get an error message saying:</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character
    string.</p>
  </blockquote>
  
  <p>but dealing with the tables individually, everything runs smoothly.</p>
  
  <p>What is the cause of this, and how do I fix it?</p>
  
  <p>EDIT:  Here is a sample of the union of the first two tables;</p>
  
  <pre><code>SELECT
      , CONVERT(DATE, [GL DATE], 101) AS [Date1]
      , CONVERT(FLOAT, [Amount_01]) AS [Amt]
  INTO #big_table
  FROM table1    
  UNION ALL
  
      [POSDATE] AS [Date1]
      , [NET] AS [Amt] NULL AS [Date_Up]
      , NULL AS [Time_up]
      ,
  FROM table2
  </code></pre>
  
  <p>Edit #2:</p>
  
  <p>I have located the trouble columns, they are 
  [Date1] and  [Amt].</p>
  
  <p>For table 1, here is sample data from both these columns:</p>
  
  <pre><code> Date1            Amt   
  4/28/2014        216.43
  4/28/2014        1526.61
  4/28/2014        13.61
  4/27/2014        177.9
  4/27/2014        489.58
  </code></pre>
  
  <p>For table 2:</p>
  
  <pre><code>  Date1         Amt
  30/04/2014       -7501.9
  30/04/2014       0
  30/03/2014       -75201.9
  30/03/2014       0
  28/02/2014       -25247.93
  28/02/2014       0
  </code></pre>
  
  <p>For Table 3:</p>
  
  <pre><code>   Date1           Amt
  21/04/2014      100,000,000.00
  21/04/2014      -2,312,314.44
  21/04/2014      -100,000,000.00
  21/04/2014      56.41
  26/04/2014      2,312,314.44
  26/04/2014      -125.4
  </code></pre>
  
  <p>If I am  not mistaken, there is a problem with the format of <code>Date1</code> in tables 2 and 3 compared to table 1, since the 2 and 3 are : <code>DD/MM/YYYY</code>, whereas table 1 is <code>MM/DD/YYYY</code>.</p>
  
  <p>But why isn't <code>Amt</code> working? can non-positive numbers not be converted to float?</p>
  
  <p>How would I address this issue and make a working conversion?</p>
  
  <p><strong>edit 3</strong>: For table 3:</p>
  
  <p>I tried using 
  CONVERT(DATE,[Date1],103) and it worked,
  but the same strategy fails for table 2, despite having the same input/output structure - what could be the reason for this?</p>
  
  <p>I again receive:</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>