Converting from integer to binary and back in SQL

问题:

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)

回答1:

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)))

回答2:

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.

回答3:

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.