SQL Server numeric format

Question

When I do the following:

DECLARE @x float
SET @x = 1234.5
SELECT CONVERT(varbinary, @x)

What is the returned format? I need to get the original number back from .net somehow, and I only have the binary value available.

Clarification:

The statement above returns

(no column name)
0x40934A0000000000

Somehow, I must get the value 12345.5 back from the binary data 0x40934A0000000000.

When you say you "need to get the original number back" I assume you mean that loosely. The round-trip through floating-point may alter your last digit(s) (it depends on how many decimal digits the numbers have and to how many digits you format them). (Of course 1234.5 will come out fine, since it's exactly representable in binary floating-point). — Rick Regan, Sep 27 '10 at 17:43
Loosely would be OK, but I really assume that convert(float, convert(varbinary, @x)) will return the number @x unmodified for any @x, so that operation could be performed in C# as well as long as you know the format. — erikkallen, Sep 28 '10 at 11:41
@erikkallen: But the decimal string in the SQL statement is the original value, right? "convert(float, convert(varbinary, @x))" round-trips the floating-point value, not the decimal value. — Rick Regan, Sep 28 '10 at 13:45
@Rick: No, this is a simplified example. Of course the real problem is more complex. — erikkallen, Sep 28 '10 at 13:57
@erikkallen: Um, I didn't mean that 1234.5 is your only value! I meant ANY decimal string in "SET @x = ...". Isn't that the value you want to recover? If so, your concern is a decimal to decimal round-trip, not a floating-point to floating-point round-trip (the latter is what you wrote in your comment). (Or maybe I'm reading too deeply, and you don't want the original decimal value, just what floating-point value it became.) — Rick Regan, Sep 28 '10 at 15:05
Did you read the question? It has nothing whatsoever to do with floating-point precision, the question is how I turn the value 0x40934A0000000000 into a number. — erikkallen, Sep 28 '10 at 22:08
@erikkallen: I don't think the question is clear enough (though now I get what you want after reading your additional comments). You have SQL which sets a floating-point variable from a decimal string, and you were looking for the original number back. I took that to mean the decimal string, since you wrote "I must get the value 12345.5 back from the binary data". (Maybe if the example had 0.1 in the SQL and you said you needed 0.1000000000000000055511151231257827021181583404541015625 back I would have understood ;) .) — Rick Regan, Sep 29 '10 at 01:23

score 0 · Answer 1 · answered Sep 27 '10 at 14:18

0

The corresponding data type in C# to SQL Server varbinary is byte[]. But, why are you converting a float to a binary if you want to access it as a number?

answered Sep 27 '10 at 14:18

D'Arcy Rittich

167,292
40
290
283

I know the data type, but I need to know the format of the actual content. The reason for me doing this is that I have a bunch of values of different types in the same (view) column, and some of them are UDTs so I can't use sql_variant. – erikkallen Sep 27 '10 at 14:29

score 0 · Accepted Answer · answered Sep 27 '10 at 14:40

Apparently the format is IEEE 64-bit, but the byte ordering is the reverse of what BitConverter.ToDouble expects. The number can be retrieved with this code:

long bits = 0;
for (int i = 0; i < 8; i++)
    bits = (bits << 8) | data[i];
return BitConverter.Int64BitsToDouble(bits);

SQL Server numeric format

2 Answers2