Thorsten Thorsten - 4 months ago 18
SQL Question

Sql server convertion to real data type

I have 2 registers transfer from PLC (modbus) to sql server, each 16 bit. I want to combine this 2 integers, and to convert to a Real sql datatype

declare @int1, @int2 integer

declare @result real

Select @int1 = 18196
, @int2 = 28800

Select @result = @int1 * 65536 + @int2 //-- this result is not wanted


I need the same result as in my Plc, the result is 38000.5

How do I convert to the real data type in sql???

My head hurts, of searching the net, thanks for help

The other way around is

select @result = 38000.5

Select convert(integer, substring(convert(varbinary, @result),1,2) //18196

Select convert(integer, substring(convert(varbinary, @result),3,2) //28800

Answer
declare  @result real 
declare @first int, @second int

set @result=38000.5


set @first = convert(integer, substring(convert(varbinary, @result),1,2)) --18196
set @second = convert(integer, substring(convert(varbinary, @result),3,2)) --28800

print @first
print @second
declare @binaryfloat varbinary(4)=convert(varbinary(2),@first)+convert(varbinary(2),@second);

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)

But as @honeybadger wrote, why not use normal types?

Comments