Convert float to varbinary and back to float

I'm trying to convert a float to varbinary, but when I try to get it back, I can't get the correct value.

For example:

select 
    convert(varbinary(max), convert(float, '11000000000000')) -> "0x42A402462F600000"

select 
    convert(varchar(max), convert(varbinary(max), convert(float, '11000000000000'))) -> "B¤F/`"

What am I doing wrong?

How can I get my float after it's been converted to binary?

The why of this question? Just to know how sql works and how manage correctly conversions data. Now, thanks @TN, i know how do it.

Solution

Updated. See my recommended solution in Addendum 2 below.

I find it strange that SQL Server will let you save the internal float representation off as binary and will not let you reverse that operation. (Makes no sense to me). It lets you do bidirectional conversions to/from binary with nearly every other type, including the various date/time types. The only other exceptions are text/ntext, but those are obsolete pointer-to-text-storage types.

I'm guessing that you are combining this with other data in a binary string and need to be able to map everything back to their exact original values.

The only alternatives I can offer are (1) Convert to VARCHAR using the lossless conversion format - CONVERT(VARCHAR(MAX), value, 3), convert from there to binary, and reverse the process to restore the original float value, or (2) Convert to the X coordinate of a Geometry::Point, from there to binary, and reverse that process (using the geometry .STX property) to restore the original float value. Both options produce binary representations several times longer than a direct conversion to binary.

It seems like you will need to choose the "best bad option."

See this db<>fiddle for a demo.

ADDENDUM:

I found a potentially better, but undocumented way of performing a float to binary to float roundtrip.

After examining the binary representation of GEOMETRY::Point(value, 0, 0), I found that its binary representation was 0x00000000010C...0000000000000000 where the ... is the reversed binary representation of the float. Using this knowledge, it is possible to take a binary representation of the original float, construct a binary representation of a GEOMETRY::Point, convert to GEOMETRY, and extract the original float value.

The following function encapsulated this technique.

CREATE FUNCTION ConvertBinaryToFloat(@FloatBinary VARBINARY(MAX)) RETURNS FLOAT
AS
BEGIN
    IF (@FloatBinary IS NULL OR LEN(@FloatBinary) <> 8) RETURN NULL

    DECLARE @PointBinary VARBINARY(MAX) =
        0x00000000010c
        + CAST(REVERSE(@FloatBinary) as VARBINARY(MAX))
        + 0x0000000000000000
    DECLARE @Point GEOMETRY = @PointBinary
    RETURN @Point.STX
END

-- Float -> binary -> float (using custom function)
DECLARE @F1 FLOAT = ACOS(-1)
DECLARE @B VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @F1)
DECLARE @F2 FLOAT = dbo.ConvertBinaryToFloat(@B)

SELECT @F1, @B, @F2
SELECT CONVERT(VARBINARY(MAX), @F1), CONVERT(VARBINARY(MAX), @F2) -- Confirm identical

This does precisely what CONVERT(FLOAT, @Binary) should do, if it were supported by SQL Server.

See this db<>fiddle that demonstrates the prior methods and this new method. Both a directly coded conversion and a function that encapsulates that conversion are shown.

Again, I stress that this is diving into undocumented representations, but I do not believe these representations would ever change because it would break all existing databases that store geometry.

ADDENDUM2:

I found yet another method that directly deconstructs the binary into components and uses them to build up the original float value. This relies on the documented representation for IEEE 754 Double Precision values.

(This solution was adapted from an answer to this question, which used a similar calculation for single precision (SQL Server real) values.)

CREATE FUNCTION ConvertBinaryToFloat(@Binary VARBINARY(MAX)) RETURNS FLOAT
AS
BEGIN
    -- IEEE 754 Double Precision binary to float
    -- Layout:
    --   64 bits total
    --   1 bit sign
    --   11 bits exponent (excess 1023)
    --   52 bits fractional mantissa  (with implicit leading 1) = 1.xxxxx
    -- Does not support IEEE NaN, +Inf, or -Inf values. 

    IF (@Binary IS NULL OR DATALENGTH(@Binary) <> 8) RETURN NULL
    IF (@Binary = 0x0000000000000000) RETURN 0
    IF (@Binary = 0x8000000000000000) RETURN -0e0 -- IEEE Negative 0

    DECLARE @Int64 BIGINT = CAST(@Binary AS BIGINT)
    DECLARE @One FLOAT = 1
    DECLARE @Two FLOAT = 2
    DECLARE @Mantissa FLOAT = @One + (@Int64 & 0x000FFFFFFFFFFFFF) * POWER(@Two, -52)
    DECLARE @Exponent INT = (@Int64 & 0x7ff0000000000000) / 0x0010000000000000 - 1023

    IF (@Exponent = 1024) RETURN NULL -- Unsupported special: Inf, -Inf, NaN
  
    RETURN SIGN(@Int64) * @Mantissa * POWER(@Two, @Exponent)
END

The equivalent conversion from Binary to Real (IEEE Single Precision) is:

CREATE FUNCTION ConvertBinaryToReal(@Binary VARBINARY(MAX)) RETURNS REAL
AS
BEGIN
    -- IEEE 754 Single Precision binary to float
    -- Layout:
    --   32 bits total
    --   1 bit sign
    --   8 bits exponent (excess 127)
    --   23 bits fractional mantissa  (with implicit leading 1) = 1.xxxxx
    -- Does not support IEEE NaN, +Inf, or -Inf values. 

    IF (@Binary IS NULL OR DATALENGTH(@Binary) <> 4) RETURN NULL
    IF (@Binary = 0x00000000) RETURN 0
    IF (@Binary = 0x80000000) RETURN -0e0 -- IEEE Negative 0

    DECLARE @Int32 INT = CAST(@Binary AS INT)
    DECLARE @One REAL = 1
    DECLARE @Two REAL = 2
    DECLARE @Mantissa REAL = @One + (@Int32 & 0x007FFFFF) * POWER(@Two, -23)
    DECLARE @Exponent INT = (@Int32 & 0x7f800000) / 0x00800000 - 127

    IF (@Exponent = 128) RETURN NULL -- Unsupported special: Inf, -Inf, NaN

    RETURN SIGN(@Int32) * @Mantissa * POWER(@Two, @Exponent)
END

Test results (double precision / SQL Server float):

Value	Binary	Result	ResultBinary	Compare	CompareBinary
0	0x0000000000000000	0	0x0000000000000000	Equal	Identical
0.5	0x3FE0000000000000	0.5	0x3FE0000000000000	Equal	Identical
1	0x3FF0000000000000	1	0x3FF0000000000000	Equal	Identical
11000000000000	0x42A402462F600000	11000000000000	0x42A402462F600000	Equal	Identical
0.333333333333333	0x3FD5555555555555	0.333333333333333	0x3FD5555555555555	Equal	Identical
142857142857143	0x42E03DB0A81DB6DB	142857142857143	0x42E03DB0A81DB6DB	Equal	Identical
1.4142135623731	0x3FF6A09E667F3BCD	1.4142135623731	0x3FF6A09E667F3BCD	Equal	Identical
3.14159265358979	0x400921FB54442D18	3.14159265358979	0x400921FB54442D18	Equal	Identical
2.71828182845905	0x4005BF0A8B145769	2.71828182845905	0x4005BF0A8B145769	Equal	Identical
0	0x8000000000000000	0	0x8000000000000000	Equal	Identical

Test results (single precision / SQL Server real):

Value	Binary	Result	ResultBinary	Compare	CompareBinary
0	0x00000000	0	0x00000000	Equal	Identical
0.5	0x3F000000	0.5	0x3F000000	Equal	Identical
1	0x3F800000	1	0x3F800000	Equal	Identical
1.1E+13	0x55201231	1.1E+13	0x55201231	Equal	Identical
0.3333333	0x3EAAAAAB	0.3333333	0x3EAAAAAB	Equal	Identical
1.428571E+14	0x5701ED85	1.428571E+14	0x5701ED85	Equal	Identical
1.414214	0x3FB504F3	1.414214	0x3FB504F3	Equal	Identical
3.141593	0x40490FDB	3.141593	0x40490FDB	Equal	Identical
2.718282	0x402DF854	2.718282	0x402DF854	Equal	Identical
0	0x80000000	0	0x80000000	Equal	Identical

See this db<>fiddle for a demo.