What will convert return?

Question

What will convert return?

Bajrang

SSCrazy

Points: 2074
More actions
July 10, 2013 at 8:07 pm

#395115

Comments posted to this topic are about the item What will convert return?
-RP

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

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 1

Good one. Actually whenever we try to convert any number data type (int, float, money, decimal,...) in Character format (char, varchar, nchar, nvarchar) and Character format length is less then number format, then it always throw error or incorrect result (*). It's a thumb rule (for me)

🙂

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Good question, oldie but a goodie.

Explanation however is incorrect. The problem is not that the value exceeds the upper limit of an integer , I'm pretty sure 5555555 can fit in an integer, but the problem is that the string 5555555 is 7 characters long and you're trying to convert it to 6 characters. This will lead to a truncation, which for some data types will give * when using the CONVERT function.

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

The explanation is not completely correct

The upper limit for INT data type is 2^31 - 1( 2,147,483,647 ), the number 5555555 does not exceed this limit

As Danny has said above, the issue is because VARCHAR(6) is not capable of accepting the 7 digit number

Change it VARCHAR(7) and you will get a proper result

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

psingla Hall of Fame Points: 3840 More actions · Answer 4

Good question..and I have seen this behavior earlier as well.

As per BOL, "*" is returned when we try to convert to char and varchar and

Error message when try on convert to nchar, nvarchar...

Any specific reason why Microsoft has implemented this way?

I would have implemented error in all cases either it be char,varchar,nvarchar or nchar to keep it simple and easy to developer 😉

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

paul s-306273 SSChampion Points: 10730 More actions · Answer 5

How about BOL for the explanation - that just says you get '*'.

Toreador SSChampion Points: 11368 More actions · Answer 6

Kingston Dhasian (7/11/2013)
The explanation is not completely correct

Worse than that, the explanation is completely incorrect 🙁

It gives two "facts" (that this number exceeds the upper limit of an integer, and that exceeding the upper limit for an integer would cause this behaviour), boith of these "facts" are false.

The number does not exceed the maximum for an integer. And if it did, it would lead to an error rather than '*'.

The '*' is because the converted number is longer than the 6 characters into which it is being put.

udayroy15 Ten Centuries Points: 1076 More actions · Answer 7

udayroy15

Ten Centuries

Points: 1076

July 11, 2013 at 5:45 am

#1631528

Good question 🙂

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 8

Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 9

Koen Verbeeck (7/11/2013)
Good question, oldie but a goodie.
Explanation however is incorrect. The problem is not that the value exceeds the upper limit of an integer , I'm pretty sure 5555555 can fit in an integer, but the problem is that the string 5555555 is 7 characters long and you're trying to convert it to 6 characters. This will lead to a truncation, which for some data types will give * when using the CONVERT function.

Yah... I guess I should be up earlier than Koen if I ever want a chance to explain something I understand. 🙂

This is it exactly... after all int in SQL is 2.1 billion as the max value... 5.5 million easily fits there, just doesn't fit into a 6 character string. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Jeff Moden (7/11/2013)
Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.

Well, more than half of those who got it wrong simply counted wrong. Quite apart from the wrong (in two ways) explanation, the use of "5555555" when the number of digits mattered with "555555" as one of the answer options is just an exercise in counting characters. Maybe the 23% who (to date) picked that particular wrong answer could have got it right if instead of "5555555" and "555555" the question had contained '1234567' and the answer '123456'. Of course by now, with all the questions requiring absolute accuracy in reading and comparing, rather than knowledge of SQL Server, it's maybe a bit surprising that almost a quarter of people fell for that trick. Perhaps after all they thought that the conversion would silently truncate, in which case I would have to share your amazement.

Tom

Jedak SSCertifiable Points: 5589 More actions · Answer 11

L' Eomot Inversé (7/11/2013)
Jeff Moden (7/11/2013)
Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.
Well, more than half of those who got it wrong simply counted wrong. Quite apart from the wrong (in two ways) explanation, the use of "5555555" when the number of digits mattered with "555555" as one of the answer options is just an exercise in counting characters. Maybe the 23% who (to date) picked that particular wrong answer could have got it right if instead of "5555555" and "555555" the question had contained '1234567' and the answer '123456'. Of course by now, with all the questions requiring absolute accuracy in reading and comparing, rather than knowledge of SQL Server, it's maybe a bit surprising that almost a quarter of people fell for that trick. Perhaps after all they thought that the conversion would silently truncate, in which case I would have to share your amazement.

Another good explanation would be that they might expect it to function the way the the string conversion works. Which is to truncate the string to specified length.

DECLARE @sTest varchar(11)

SET @sTest = '12345678901'

SELECT convert(varchar(10),@sTest)

--Shows 1234567890

Bajrang SSCrazy Points: 2074 More actions · Answer 12

Thank you SSC for posting this question.. 🙂

and

Thank you all members.. Yes, explanation provided on BOL link doesn't fit but couldn't find any other source to support this. 😀

-RP

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 13

Bajrang (7/11/2013)
Thank you SSC for posting this question.. 🙂
and
Thank you all members.. Yes, explanation provided on BOL link doesn't fit but couldn't find any other source to support this. 😀

The explanation provided in BOL is correct. Check the block Truncating and Rounding Results in the link you have provided.

Correct answer: *

Explanation:

Since this value exceeds the upper limit of an integer, a * is returned.

Ref: http://msdn.microsoft.com/en-us/library/ms187928.aspx%5B/quote%5D

The explanation that you have provided above is actually incorrect because of the following reasons

1. The value "5555555" does not exceed the limit for INT data type

2. An asterisk(*) is returned because you are trying to convert a 7 digit integer to VARCHAR(6).

If you try to convert it to VARCHAR(7) or greater it will give you the expected answer.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Bajrang SSCrazy Points: 2074 More actions · Answer 14

The explanation provided in BOL is the link I have provided!! Are you saying, one is correct but not the other!!!

" * = Result length too short to display. E = Error returned because result length is too short to display." in section [Truncating and Rounding Results]

-RP