Data types in tables

Question

Post reply

Data types in tables

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
June 10, 2013 at 9:33 pm

#394942

Comments posted to this topic are about the item Data types in tables
Malleswarareddy
I.T.Analyst
MCITP(70-451)

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Thanks Mr. Reddy!

Interesting question... I was able to solve this as I have worked such issues in the recent past.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Good question 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Good question. Too bad the explanation is lacking. It explains how to work around the issue, but not what causes it.

The answer to that is data type precedence. Both VALUES and "SELECT ... UNION SELECT ..." are expressions that can be used at various places, not just in an INSERT. Expressions that return a resultset - a set of zero^*, one, or more rows, with one or more columns. Each of those columns has to have a single data type. And that data type is determined by those rules of data type precedence.

In this case, the input matches two data types: date and varchar. Date has a higher precedence than varchar, so the resultset of both the VALUES and the UNION'ed queries is defined as having a single column with data type date. This means that the varchar value will be explcitly converted - and that is what causes the error. If you modify the question to replace the varchar value '10' with a value that does convert to date (eg '20130611'), you'll get no errors, and two rows in the returned result. That have come there after first] converting the varchar value '20130611' to its date equivalent, and then converting both that date and the other (unconverted) oct 10 date to varchar, for inserting in the table.

Explicitly casting the date argument to varchar fixes this because (a) that cast succeeds, (b) now both inputs for VALUES / UNION are the same data type (varchar), so no conversion is required, and (c) the result of that VALUEUS / UNION is also already the correct data type for immediate inserting in the table.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 4

When I run the 2nd query it inserts 2 rows into the table.

So my answer would be 'error', '2 rows', '2 rows'.

If I remove the insert from the 2nd query, I get an error.

declare @test-2 table (Value varchar(50));

declare @date date ='10-10-2012'

declare @t1 varchar(10)

set @date='10-10-2012'

--insert into @test-2

select '10'

union

select @date --cast as varchar

select * from @test-2

The data type preference depends not only on the union, but takes the table into account also.

Running on SQL 2008 R2

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Louis Hillebrand (6/11/2013)
When I run the 2nd query it inserts 2 rows into the table.
So my answer would be 'error', '2 rows', '2 rows'.
If I remove the insert from the 2nd query, I get an error.
declare @test-2 table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)
set @date='10-10-2012'
--insert into @test-2
select '10'
union
select @date --cast as varchar
select * from @test-2
The data type preference depends not only on the union, but takes the table into account also.
Running on SQL 2008 R2

I am also using SQL 2008 R2 it gives me an error when i ran it.

Error:

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

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

Nice question 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

Louis Hillebrand (6/11/2013)
When I run the 2nd query it inserts 2 rows into the table.
So my answer would be 'error', '2 rows', '2 rows'.

That's strange...

What is the data returned by the final SELECT?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 8

Louis Hillebrand

SSCarpal Tunnel

Points: 4982

June 11, 2013 at 2:36 am

#1622759

The final select returns '10' and '2012-10-10'

Louis.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

Louis Hillebrand (6/11/2013)
When I run the 2nd query it inserts 2 rows into the table.

Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'

That's very strange. I cannot reproduce this (SQL 2012), and I have no explanation.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Richard Warr SSCertifiable Points: 6966 More actions · Answer 10

Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'
Louis.

I get that too. Perhaps it's because I'm in the UK.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

Richard Warr (6/11/2013)
Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'
Louis.
I get that too. Perhaps it's because I'm in the UK.

I also get that. Hugo, what is your collation and locale?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'
Louis.

Yes final statement will gives this output while for rest two statement it will gives you an error definitely

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 13

Koen Verbeeck (6/11/2013)
Richard Warr (6/11/2013)
Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'
Louis.
I get that too. Perhaps it's because I'm in the UK.
I also get that. Hugo, what is your collation and locale?

I am also getting the error message and my collation is "SQL_Latin1_General_CP1_CI_AS"

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 14

Lokesh Vij (6/11/2013)
Koen Verbeeck (6/11/2013)
Richard Warr (6/11/2013)
Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'
Louis.
I get that too. Perhaps it's because I'm in the UK.
I also get that. Hugo, what is your collation and locale?
I am also getting the error message and my collation is "SQL_Latin1_General_CP1_CI_AS"

same I have

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/