6

I've been banging my head against the wall with this one all morning.

The following SQL code and its' result makes no sense to me:

select CONVERT(INT, CONVERT(BINARY(30),2691485888))

which results in:

-1060082528

What? Why doesn't the result equal my original integer?

My whole objective is to convert an integer into bytes and store those bytes into the database, but without getting this basic example to work I am stuck. Can anyone explain what I'm doing wrong?

By the way, I am using Sql Server 2005 (9.0.4340)

Improve this question
4
  • Can you explain why you need to convert the integer into bytes? Why can't you store it in an INT column? Commented Sep 14, 2011 at 19:28
  • @kekekela - If the reasoning behind this is known, an better alternative might be the answer. Context is important. Commented Sep 14, 2011 at 19:30
  • 2
    For one thing, 2,691,485,888 is larger than what an INT can hold. Commented Sep 14, 2011 at 19:31
  • Use unsigned int instead of int ;p Commented Aug 12, 2015 at 9:54

3 Answers 3

Reset to default
12

As I noted in my earlier comment, 2,691,485,888 is larger than what an INT can hold.

This will work:

select CONVERT(BIGINT, CONVERT(BINARY(30), CONVERT(BIGINT, 2691485888)))
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ah! This makes sense. I always was at a loss as to why I was unable to cast, say, 5B to binary, but it was because it was implicitly casting 5B to int before the explicit cast to binary. Thanks much.
Yep the implicit cast was what I was missing too.
You can also just make it unsigned to keep it a normal 4 byte int
4

The value 2691485888 cannot be held in an INT - it is too large:

int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes

There is a good chance you are seeing the result of an overflow.

A data type that can handle that value is BIGINT.

Improve this answer

1 Comment

That makes sense; but is there any way to get around it?
1

2691485888 is beyond the upper-bound of the integer datatype (which is 2147483647)

If you convert it to bigint, it should result in the correct amount.

Improve this answer

2 Comments

but select CONVERT(BIGINT, CONVERT(BINARY(30),2691485888)) returns 720575947909131424 which is also incorrect...
@Slider345: The reason you get an unexpected answer is because SQL Server interprets the literal value 2691485888 to have type DECIMAL(10,0). The conversion to binary(30) and then to bigint will only get you back where you started if you started with a bigint. Note that this gives you the result 2691485888: SELECT CONVERT(DECIMAL(10,0), CONVERT(BINARY(30), 2691485888))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.