9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alexander Pavlenko
  • 325
  • 2
  • 12
  • 3
    why do you save a 64-bit integer as varbinary in the first place? – BrokenGlass Dec 11 '11 at 20:13
  • Typically you would store `long` in C# as `bigint` in SQL Server. Is there a particular reason you are trying to store `long` as `varbinary`? – rsbarro Dec 11 '11 at 20:13
  • Because that DB field don't contains only int64 type values. – Alexander Pavlenko Dec 11 '11 at 20:16
  • You could create a C# assembly and install it using SQL-CLR into SQL Server to handle this conversion back to `Int64` again.... – marc_s Dec 11 '11 at 20:20
  • Your questions is absolutly right but in my case I just refator already existed solution. I want to divide that column for new others by type. And I need to transmit data from old column to new without any lose and actually I can't use .NET applications or CLR Intergating for my aim. – Alexander Pavlenko Dec 11 '11 at 20:24

1 Answers1

14

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);
Jon Hanna
  • 110,372
  • 10
  • 146
  • 251
  • Thank you for your explaination of this issue. But I don't planing to save 'long' values to 'varbinary' again. I just need some tool to get already existing 'bigint' values in that column like I get values within '.NET BitConverter.ToInt64'. I need something like 'Array.Reverse(savedValue);' only within 'TSQL' query environment – Alexander Pavlenko Dec 11 '11 at 20:54
  • 2
    Thanx Jon I found! In that case I need to use `reverse` function in `TSQL`: `select cast(cast(reverse(Value) as varbinary(max)) as bigint) from dbo.MyValues` – Alexander Pavlenko Dec 11 '11 at 21:09
  • 1
    Regardless of the asker's DB design, this question helped me solve a similar problem when trying to implement a C# equivalent of a T-SQL HASHBYTES function. I was not even aware of the possibility of the endian order being different. Thanks! – Dan Bailiff Jun 20 '16 at 18:31