SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 LEN(@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
  
    DECLARE @Result FLOAT =  SIGN(@Int64) * @Mantissa * POWER(@Two, @Exponent)
    RETURN @Result
END
SELECT
    V.Value, B.Binary, R.Result,
    CONVERT(VARBINARY, R.Result) AS ResultBinary,
    CASE WHEN V.VALUE = R.Result THEN 'Equal' ELSE 'Different' END AS Compare,
     CASE WHEN B.Binary = CONVERT(VARBINARY, R.Result) THEN 'Identical' ELSE 'Different' END AS CompareBinary
FROM (
    VALUES
        (0e0),
        (0.5e0),
        (1e0),
        (convert(float, '11000000000000')),
        (1e0/3e0),
        (1e15/7e0),
        (SQRT(2e0)),
        (ACOS(-1e0)),
        (EXP(1e0)),
        (-0e0)  -- IEEE Negative zero
) V(Value)
CROSS APPLY (SELECT CONVERT(VARBINARY, V.Value) AS Binary) B
CROSS APPLY (SELECT dbo.ConvertBinaryToFloat(B.Binary) AS Result) R


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


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 LEN(@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
    DECLARE @Result REAL =  SIGN(@Int32) * @Mantissa * POWER(@Two, @Exponent)

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

    RETURN @Result
END
SELECT
    V.Value, B.Binary, R.Result,
    CONVERT(VARBINARY, R.Result) AS ResultBinary,
    CASE WHEN V.VALUE = R.Result THEN 'Equal' ELSE 'Different' END AS Compare,
    CASE WHEN B.Binary = CONVERT(VARBINARY, R.Result) THEN 'Identical' ELSE 'Different' END AS CompareBinary
FROM (
    SELECT CONVERT(REAL, V.Value) AS Value
    FROM (
        VALUES
            (0e0),
            (0.5e0),
            (1e0),
            (convert(float, '11000000000000')),
            (1e0/3e0),
            (1e15/7e0),
            (SQRT(2e0)),
            (ACOS(-1e0)),
            (EXP(1e0)),
            (-0e0)  -- IEEE NEgative zero
    ) V(Value)
) V
CROSS APPLY (SELECT CONVERT(VARBINARY, V.Value) AS Binary) B
CROSS APPLY (SELECT dbo.ConvertBinaryToReal(B.Binary) AS Result) R


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

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

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