-1

I am trying to use SELECT INTO and UNION ALL to join 3 tables together.

Table 1 uses a number of Ltrim(Rtrim()) and CONVERT commands, whilst with the other two tables, I am just taking their columns out and pasting them into this combined table.

When I try to run the query, I get an error message saying:

Conversion failed when converting date and/or time from character string.

but dealing with the tables individually, everything runs smoothly.

What is the cause of this, and how do I fix it?

EDIT: Here is a sample of the union of the first two tables;

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

Edit #2:

I have located the trouble columns, they are [Date1] and [Amt].

For table 1, here is sample data from both these columns:

 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

For table 2:

  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

For Table 3:

   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

If I am not mistaken, there is a problem with the format of Date1 in tables 2 and 3 compared to table 1, since the 2 and 3 are : DD/MM/YYYY, whereas table 1 is MM/DD/YYYY.

But why isn't Amt working? can non-positive numbers not be converted to float?

How would I address this issue and make a working conversion?

edit 3: For table 3:

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?

I again receive:

Conversion failed when converting date and/or time from character string.

Morpheus
  • 1,616
  • 1
  • 21
  • 31
elbarto
  • 211
  • 3
  • 15
  • Look for those columns which are converted to datetime from string, it may be something related to the format of the date values in the column. – Harsh Dec 15 '15 at 01:08
  • but, when running the query separately, it works fine. Isn't that odd? Or is there some problem with using a `UNION ALL` on two tables with one row entry being converted from string to date/time and the other remaining as a string, but belonging to the same column? – elbarto Dec 15 '15 at 01:12
  • 1
    If for example you are aligning a column of type `DATETIME` and a column of type `VARCHAR` then it will try and automatically convert the `DATETIME` column to a `VARCHAR` and in your case fail. Remove columns from your statement until you find the offending column pair, identify the data with the issue then post back and we can help further – Nick.Mc Dec 15 '15 at 01:17
  • 1
    It's not odd that running them separately is working because it's not doing any implicit casting. – Nick.Mc Dec 15 '15 at 01:19
  • @Nick.McDermaid I see, that makes sense. Theoretically, then, if I apply the exact same conversions to my other tables, it should work? Also, what If I just enter `NULL` values for the columns in the bottom two tables, where I am using `CONVERT` in the top table – elbarto Dec 15 '15 at 01:37
  • NULLs are fine. Yes if you apply identical conversions it should work. Coverting VARCHAR representations of dates can be tricky though because they are often in an inconsistent format. Make sure that if you get an error you use the divide and conquer method to isolate the column and data. That includes removing the `INTO` to work out whether the error is due to `UNION ALL` or whether it's due to inserting into a table. – Nick.Mc Dec 15 '15 at 03:13
  • In your example, what datatype is `table2.POSDATE` is it a `VARCHAR` or `datetime` or `date`. This is all about datatypes so you need to post them. – Nick.Mc Dec 15 '15 at 03:14
  • 2
    Oh... and `FLOAT` is bad data type for storing money. – Nick.Mc Dec 15 '15 at 03:15
  • Hey Nick, they are all VARCHAR. I think I am understanding what is happening now so will do some detective work and post back. Unfortunately I was not the one who entered the data, so maybe someone entered 'asdf' (or something equally as wrong) in the [amount] column and that's where the problem lies. – elbarto Dec 15 '15 at 03:25
  • I have updated with some sample rows – elbarto Dec 15 '15 at 03:42
  • The real problem is that the correct data types should have been chosen at the start. Those fields should have been `DATE` or `DATETIME`, not varchar – Nick.Mc Dec 15 '15 at 10:17
  • You need to check all the data - if they are VARCHAR they could be anything and won't necessarily follow any format or even contain anything that looks like dates – Nick.Mc Dec 15 '15 at 10:18
  • @Nick.McDermaid This was precisely the problem. there were two very random entries in the table which I found. Once handled, everything worked fine. Also, I used `MONEY` instead of `FLOAT` which worked as well. I believe the commas `,` were causing issues thanks. – elbarto Dec 16 '15 at 01:16
  • If you have the opportunity you should fix the datatypes in the source, though this is usually such a huge job it never gets done. – Nick.Mc Dec 16 '15 at 02:07
  • Thanks for all your help @Nick.McDermaid ! – elbarto Dec 17 '15 at 01:04

1 Answers1

2

SELECT INTO will create a table, and in doing so it will make assumptions about what data types to use when creating the table if you have applied conversions to some of the columns. It should be pretty simple to look at the two existing tables and note the date/time/string fields, and make sure that the UNION columns that match these columns are typed correctly.

So, for instance, if column 1 in your other two tables are both strings, and column 1 in your SELECT INTO table is a date/time, you can use SELECT convert(varchar,Col1) AS yourColName, so that it will merge with the string type in the other two tables.

It would help if you posted your query code and table definitions.

KWallace
  • 1,570
  • 1
  • 15
  • 25
  • Thanks, I see. I have added some sample code. What's the best way to work around this, If at all costs i need to preserve the way table 1 is being handled? Do i need to apply the same conversions to the other tables' relevant row entries? – elbarto Dec 15 '15 at 01:36
  • 1
    The best workaround is to create the table up front via `create table`. – shawnt00 Dec 15 '15 at 04:33
  • How did it go? Did you get it working right? Good luck, my friend! – KWallace Feb 19 '16 at 16:25