tsql - SQL Server varbinary bigint with BitConverter.ToInt64 values are different

Question

I save my long value in a SQL Server table as varbinary(max):

var savedValue = BitConverter.GetBytes(longValue);

Now I need to work with that value in T-SQL query, but when I trying to get value:

select cast(Value as bigint) from dbo.MyValues

It returns different number value. For example if I saved -8588797048854775808 in .NET, in T-SQL I get 33802181122903688

Please tell me what's the problem? Have that issue any solution?

Answer 1

Casting from varbinary to bigint (and back) uses network byte order (big-endian). BitConverter uses the endian-ness of the machine it is run on (little-endian for x86 and x64).

Hence BitConverter.GetBytes run on -8588797048854775808 (0x88CE7696E7167800) is {0x00,0x88,0xE9,0x18,0x69,0x89,0x31,0x77}, and cast on {0x00,0x88,0xE9,0x18,0x69,0x89,0x31,0x77} is 0x0088E91869893177 = 38536887891734903.

The obvious thing to do is to just store 64-bit integers as 64-bit integers in the first place.

If you really need to do this conversion then:

var savedValue = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(longValue))

Will swap around the bytes, while also being portable in that it won't swap the bytes if run on a big-endian machine.

Alternatively, if you don't want to use the System.Net namespace for some reason, or if you want to be extensible to types other than the three IPAddress.HostToNetworkOrder handeles, use:

var savedValue = BitConverter.GetBytes(longValue);
if(BitConverter.IsLittleEndian)
  Array.Reverse(savedValue);