How do I convert a hex to a floating point number? as in 0x43F8E354 should be 497.776
This is SQL Server 2008 R2
I've tried http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-23-fn_replinttobitstring.htm, but it does not look as if fn_replinttobitstring exists in Sql Server 2008 R2.
In SSIS I can do like this:
But how to do this in T-SQL?
There is a great discussion of this topic on the main SSC site which includes Jeff Moden, Peter Larsson (whom we know as @Peso on this site) and Martin Wills. Here is the link: http://www.sqlservercentral.com/Forums/Topic356756-8-1.aspx. I just tried the version which Martin Wills posted on page 3 which was in turn a recoding of Peter's code. Here is the paste from the post referenced above:
Here is the test:
Please note that the desired number 497.77600098 is not real (in both senses) because it has way too many digits for a real (a.k.a. float(24)) number to afford. However, if the accurate binary representation 0x43F8E354 of the single floating point number 497.776 is sufficient then the function above should help.