T-SQL 2012: Can't get Convert to Work with UNION

Question

I'm trying to combine the records from two tables using the UNION clause.  I am having problems converting a datatype in one of the fields in Table1 to get it to match the data type of the same field in Table 2

Table1.FieldA data type is Varchar(1000) but the data in the field is 5/2/12, 6/15/12, etc.  Date type data.

Table2.FieldA data type is Date.

I'm combining the fields of Table1 with Table2 but keep getting a data conversion error.

This is how my syntax is setup:

Select Convert(Date,Select Table1.FieldA) AS FieldA From Table1  --When data conversion runs fine when I run this Select statement by itself??

UNION

Select Table2.FieldA From Table2

Any suggestions would be appreciated.

Regards,

Bob Sutor

Answer 1

Try

cast('5/4/12' as datetime)

or

select CONVERT(datetime,'11-6-12',11)
go

---

Many Thanks & Best Regards, Hua Min

Answer 2

DECLARE @d Varchar(1000)='5/2/12'
--PRINT @d
  
--SELECT CAST(@d AS DATETIME)
SELECT CONVERT(VARCHAR(10),@d,101) --5/2/12
SELECT CONVERT(VARCHAR(10),CAST(@d AS DATETIME),101) --05/02/2012
SELECT CONVERT(VARCHAR(8), CAST(@d AS DATETIME), 1)  -- 05/02/12

---

Narsimha

Answer 3

For some reason the Convert function doesn't work when placed within the context of a UNION statement??

...bob

Answer 4

This should work -

DECLARE @d2 DATE='01/07/2012'
DECLARE @v1 VARCHAR(10)='01/08/2012'

SELECT @d2
UNION
SELECT @v1

Select Convert(Date,@v1) 
UNION
Select @d2

Also, why there's a Select inside a Select when quering from the same table1

Select Convert(Date,Table1.FieldA) AS FieldA From Table1  --When data conversion runs fine when I run this Select statement by itself??

UNION

Select Table2.FieldA From Table2

---

Narsimha

Answer 5

I'm trying to get Convert to work with a Union clause it keep getting a conversion error??

This is what I'm trying to do:

Select Convert(Date,Table1.FieldA) AS FieldA FROM Table1

UNION

Select Table2.FieldA FROM Table2

Table1.FieldA is VarChar(1000) and contains the following consistent data (5/1/12, 6/15/12, 8/15/12, etc.)

When I run the Select Convert(Date,Table1.FieldA) AS FieldA FROM Table1 by itself it works fine.  All data converts to dates.

When I run the entire code set including the UNION and the two select clauses I get the conversion error.

Does anyone have some thoughts on this?

Thanks,

Bob Sutor

Answer 6

Make sure Table2.FieldA is also with datetime data type. Or consider to use cast it, like

...

union

select cast(Table2.FieldA as datetime) FROM Table2;

---

Many Thanks & Best Regards, Hua Min

Answer 7

declare @temp table(date1 varchar(10))
insert into @temp values('5/1/2012')

declare @temp1 table(date1 date)
insert into @temp1 values('6/1/2012')

Select Convert(Date,date1)  FROM @temp
UNION all
Select date1 FROM @temp1

---

Please mark as 'Answer', if the solution solves your problem.

Answer 8

okay,

I tried this on a 2012 server and was running perfectly fine

declare @tab1 table(
dateval varchar(1000));

insert into @tab1 values('5/2/12'),( '6/15/12');

declare @tab2 table(
dateval date);
insert into @tab2 values('8/2/12'), ('7/15/12');



select convert(date,dateval) from @tab1
union all
select * from @tab2

Can you try without a explicit convert?

select * from @tab1
union
select * from @tab2

this too worked without any trouble on 2012

Regards
satheesh

Answer 9

Same thread,

http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/98ca0c90-3c35-4f83-baed-0bcac97a6885

Regards
Satheesh

Answer 10

Apparently there is data in Table2.FieldA that cannot be converted to date.

In a query, all cells have the same data type, so if you have query like:

SELECT col_of_thisdatatype FROM tbl1
UNION
SELECT col_of_thatdatatype FROM tbl2

There has to be data-type conversion somewhere. What happens is that SQL Server looks at the two data types, and then applies it's data-type precedence, and attempts to convert the type with lower precedence to the other type. If there is no implicit conversion, you get a compile-time error. If there is data that cannot be converted to the other type, you get a run-time error.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

select * from @tab1
union
select * from @tab2

This Works perfectly.

Doesn't require conversion.

select * from @tab1
union
select * from @tab2
Union 
Select 'Date' 

Above will give error because its character values and SQL Server not recognize as data type date.

This will work.

declare @tab1 table(dateval varchar(1000));

insert into @tab1 values('5/2/12'),( '6/15/12');

declare @tab2 table(dateval date);

insert into @tab2 values('8/2/12'), ('7/15/12');

select * from @tab1
union all
select CONVERT(varchar(100),dateval) from @tab2
union
select 'Dates'

1st Query in Union takes as data type of  columns in SELECT.

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 12

Hi,

If possiblel you may convert varchar datatype of the column into date in table schema. This is better approch while you storing date in column. It may help you to save disk space also.

Thanks,

Ashru

---

MCTS, http://asharafaliptb.wordpress.com

Answer 13

I suggest to convert to varchar the date column rather than trying to convert varchar to date. Any date can be converted to varchar but not all varchar data can be converted to date. You can try

select FieldA from Table1

UNION -- union removes duplicates 

select convert(varchar(10), FieldA) from Table2

This will eliminate the error you're getting

You can also try

select case when ISDATE(FieldA) = 1 then convert(date, FieldA) end as FieldA from Table1

UNION

select FieldA from Table2

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

>> I'm trying to combine the records [sic: rows are not records] from two tables using the UNION clause [sic: operator, not clause]. I am having problems converting a data type in one of the fields [sic: columns are not fields] in Table1 to get it to match the data type of the same field [sic] in Table 2 <<

Your first problem is that you do not even know the correct terms. 

>> table1.field_a Data type is VARCHAR(1000) but the data in the field [sic] is 5/2/12 [sic: '2012-05-12'], 6/15/12 [2012-06-12], etc. DATE type data. << 

Why do you need a thousand letter to hold a date? This is called “garbage dumping” and it refers to the practice of inviting garbage data by using over-sized, improperly types columns. Fields have no data types or constraints, so people who confuse fields and columns do not get anything right. 

Table2.field_a [sic] data type is DATE. We never use CONVERT() any more. That is an old 1970's Sybase string function. SQL programers use CAST() and do display formatting in the presentation layers. The only display format allowed in ANSI/ISO Standard SQL is ISO-8601 'yyyy-mm-dd' and T-SQL follows the rules. You need to try it, too. 

You probably also do not know that the result of a UNION operator is a table whose columns have no names. You need to take care of this. The two CAST() are just for safety. 

SELECT X.foobar_date
 FROM (SELECT CAST (field_a AS DATE)
         FROM Table1
       UNION
       SELECT CAST (field_a AS DATE)
         FROM Table2)
       ) AS X(foobar_date); 

But obviously, you need to so an ALTER TABLE and make all of the columns that hold dates into DATE data types. I would also be good idea to rewrite all the code this guy wrote and fire him immediately. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 15

CELKO--You need to go back to writing books and quit trying to communicate with humans.  You are not good at it.  The Var(1000) was a system generated data type.  Next time try to focus on the issue and park your larger than life ego.

Answer 16

Actually, I don't think the issue is actually converting either of the two fields to Date values.  Both fields convert the data successfully to the Date data type when running the Convert function.  The Table1 (Varchar) date converts successfully to Date and the Table2 (smalldatetime) data can successfully get converted to Date values. 

The problem is that when the two statements are ran together with the UNION statement.  I get the following conversion error:

"Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'V196' to data type int."

I'm converting both fields to Date data type so they should be the same.  For some reason when you run the two select statements together with UNION. That is where the error occurs.  I am aware the UNION requires the data types of corresponding fields to be the same.  They are both converted to DATE.  Don't quite understand why I'm getting this error?

Bob Sutor

Answer 17

Can you post the exact query statement? As you can see, the error is converting value to int type so I suppose this error comes from a different part of the query, say, from the WHERE clause. Also, are you using views or tables directly?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

OK, so it is time for a lecture.

When you ask a question, please include a reasonable amount of information. For instance, the query you have, the error message and which version of SQL Server you are using.

Had you cared to include this, you would have been closer to a solution. Now you included only fragments of information which lead us astray.

Obviously in your query you are convering a character column to integer, implicitly or explicitly. Apparently, in lucky cases, the bad character data does not always have to be converted, so the query passes. However, when you change something, like adding a new operator, the optimizer goes for a different query plan. And UNION has a huge impact, as UNION implies DISTINCT.

You first need to find where you have this character conversion. Maybe you are in error. Maybe there is bad data in the table, and the table should be fixed and not permit this bad data - that is the column in question should be integer. Maybe this is legit.

If you are on SQL 2012, you can use try_convert, then the conversion will return NULL when the data is not an integer. On SQL 2008 or earlier, you can do:

  CASE WHEN isnumeric(charcol) = 1 THEN convert(int, charcol) END

Although this is not waterproof, as isnumeric will return 1 for values that converts to other numeric data types.

And next time you post a question, please include query, error message and SQL Server version. Promise?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 19

>>Conversion failed when converting the varchar value 'V196' to data type int."

Are you including any columns other than the dates? if yes, look of r other columns as well.

Regards
Satheesh