Converting VARBINARY to FLOAT in T-SQL

This issue got me hitting my head against a wall for some hours. I found the solution posted at http://www.sql.ru/forum/actualthread.aspx?tid=314840, which in turn refers to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849 as the original source. The authors did a great job, and I cannot be more thankful as their efforts saved me some hair on my head:) Just want to share it in case someone else desperately searches for the solution (and the full credit goes to the authors of the above-mentioned posts).

So, here is it, converting from varbinary(8) to 8 byte float:

DECLARE @BinaryFloat AS VARBINARY(8);
SET @BinaryFloat = 0x3FF199999999999A; -- Binary representation for 1.1

SELECT SIGN(CAST(@BinaryFloat AS BIGINT))
  * (1.0 + (CAST(@BinaryFloat AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52))
  * POWER(CAST(2 AS FLOAT), (CAST(@BinaryFloat AS BIGINT) & 0x7ff0000000000000) / 0x0010000000000000 - 1023)
-- Returns 1.1

And converting from varbinary(4) to 4 byte float:

DECLARE @BinaryFloat AS VARBINARY(4);
SET @BinaryFloat = 0x3FC00000; -- Binary representation for 1.5

SELECT SIGN(CAST(@BinaryFloat AS INT))
  * (1.0 + (CAST(@BinaryFloat AS INT) &  0x007FFFFF) * POWER(CAST(2 AS REAL), -23))
  * POWER(CAST(2 AS REAL), (CAST(@BinaryFloat AS INT) & 0x7f800000) / 0x00800000 - 127)
-- Returns 1.5

Hope this helps and saves someone some time.

BTW, anyone knows why casting from varbinary to float is not supported in SQL Server?