Convert A Binary Float To FLOAT Datatype

Apr 9, 2007

I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.

All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little


Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)

SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))

SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]

And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT

SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END

RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!


Peter Larsson
Helsingborg, Sweden

View 3 Replies


ADVERTISEMENT

How To Convert Nvarchar Datatype To Float

May 16, 2008

Hi,

how to convert nvarchar datatype to float?

Regards
Prashant

View 10 Replies View Related

Function Required To Convert Packed Decimal In Binary(6) To Int (or Float)

Mar 8, 2007

I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.

The field contains values such as the following:-

0x20202020200C

0x202020022025

0x20202020DFFA

I don't know how to interpret these. Has anyone got a function that can do this for me?

I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?

Thanks!

View 2 Replies View Related

Can Anyone Help With With Datatype Float

Aug 30, 2007

I have some engineering data in my table and the db designer is representing it with a float datatype. Here's what is happening. If I query on a record based on id num and get a row and put it in text boxes in my Windows App, min_riv_hd_dia (the float) is 0.026<14 zeroes>2. If I try to query and get that same record again but this time based on id num and min_riv_hd_dia equal to 0.026<14 zeroes>2, I get no row found. If I just do a select on this row based on id number, sql server displays it as 0.026. But if I query with 0.026 as my value, still the row is not found. If I query min_riv_hd_dia > 0.026, the record is found.

So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?

Thank you so much if you can help!

View 4 Replies View Related

How To Format Float Datatype?

Apr 5, 2000

For example, I have a float datatype field with a value of .2501
I select using ROUND(Field,3) and get .25 as a result, but what I need to display is .250 (three decimal places.) How can this be done?
Thanks,
Paul

View 3 Replies View Related

Sum Float Datatype Column

Feb 12, 2014

I'm using this query to sum the values. The cost column is a float datatype and what could I differently do here to sum the cost. I'm unable to sum the cost.

Also is there any way I change the datatype to int for Cost column without losing the data.

select ID, MAX(Date) Date, SUM(Cost) Cost, MAX(Funding) Funding from Application
group by ID

View 4 Replies View Related

What DataType Is Best ... Float, Double Or Decimal?

Sep 25, 2006



I am using sql express 2005 and sql server 2005 with C# 2.0.

I am a bit confused about which data type i should be using for several fields.

Right now I am declaring all of my fields in sql server as float for everything except for money fields which are using money.

When loaded into C# these fields are converted to double and decimal because C# does not have a float datatype.



Should I be using Decimal or Double for everything instead?

Here are a few examples

QtyInvoiced (float) - holds the number of items invoice

possible values look like this 1.0, 1.25 or 1.5



PercentDiscount (float) - holds a percentage

possible values look like this

10.25, 20.50, 50.00



I appreciate the help.

View 9 Replies View Related

Wrong Values Stored For Datatype Float

Aug 3, 2006

In SQL Server for a field of datatype float(8),
if i insert a value 2.62 , it saves it as 2.6200000000000001
like so for other values also.
But in frontend i can see the right values.

Could any of you help me in fixing up this issue?

Thanks in advance.

~ chaitanya

View 1 Replies View Related

Is It Possible To Change The Datatype In A Excel File From The Float To Varchar

Sep 10, 2007

Hi,
   I have a excel file and i am trying to import zip codes to the database... but the some of the zip codes start with 06902 but the excel file treats them as float but i want to treat them as varchar...
 
How can i do it.
Regards
Karen

View 2 Replies View Related

Wrong Values Stored In A Field Of Datatype Float

Aug 3, 2006

Hi,

When i try to insert a value in to a field of datatype float(8), it is storing the wrong values(approximate values).

For example, if i try to insert 2.62 , it takes it as 2.6200000000000001

But i can see the correct values displayed in the frontend.



Heard that there is a fix available for this issue.

Could any of you help me in getting the details about that fix?



Thanks in advance.

~ Chaitanya



View 4 Replies View Related

Convert Int To Float

Jun 21, 2007

Hi,

why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lot

View 14 Replies View Related

SQL Server 2008 :: Difference Between Money And (Float Or Decimal) Datatype

Jan 16, 2013

What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?

View 4 Replies View Related

Convert Float To Char

May 23, 2008

hi, how can I convert a float to a char so I can do:

'£' + convert(char,amount) AS [money]

When I do the above the number is like 1.07848e+006
rather than 1078480


thans for any help.
jamie

View 20 Replies View Related

Convert Float To Varchar(20)

Apr 11, 2006

I have a sp that receive a rango of float values,and I need to convert this values to a varchar(20).

I am trying the next but I got a strange result.

@intNoTarjIni = 121456790
set @strTarjeta=cast(@intNoTarjIni as varchar(20))
returns: 1.21457e+008

set @strTarjeta=convert( varchar(20),@intNoTarjIni)
returns:1.21457e+008

How can I convert sucessfully a float to varchar?

View 2 Replies View Related

Convert Float To Varchar

Jun 26, 2007

Hi -

My field TDMergerVotePercent is defined as a float field. I want it to return 'NA' when the value is -1 but I'm getting the error message

'Error converting data type varchar to float' in my aspx page. Is there where the CAST function can be used? Thanks

CASE
WHEN TDMergerVotePercent = -1 THEN 'NA'
WHEN TDMergerVotePercent IS NULL THEN ''
ELSE TDMergerVotePercent
END AS TDMergerVotePercent

View 4 Replies View Related

Convert Float To Nvarchar

Feb 15, 2008

I have a data type float with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried cast(field as nvarchar) however this is not working. What might fix this? I cannot change the insert table data type.

View 3 Replies View Related

How To Convert From Ntext To Float?

Jul 23, 2005

Hello,I would like to convert a field from ntext field found in one databasetable to float field found in another database table. The reason why Iwant to do this is a long one.I have tried the following and playing around with the following:declare @valuePointer varbinary(16)<Row cursor logic to initialize @valuePointer to be a pointer to thesource ntext field>update TargetFloatTable set TargetFloatTable.TargetFloatValue =CAST(CAST(@valuePointer AS nvarchar) AS float)where TargetFloatTable.Id = @Idbut is not working for me.Hoping someone out there can help.Thanks,Cally

View 5 Replies View Related

Convert Float To Char

Aug 26, 2005

Select Cast('100.1234' as float)give me the result 100.1234Now when I convert it back to char I want exactly 100.1234Select Convert(char(100),Cast('100.1234' as float))Gives me 100.123 (Here I was expecting 100.1234)When I doSelect STR(Cast('100.1234' as float),25,4)I get back the result as 100.1234However here I am not sure how many digits do I have after the decimalpoint. If I put some value likeSelect STR(Cast('100.1234' as float),25,8)I get 0's appended to it, which is again not desired.Thanks in advance,Jai

View 4 Replies View Related

Convert Varchar To Float?

Mar 7, 2008

Is this possible? I'm trying to user a lookup task and the data I want to compare is a varchar to float. How can I do this? I tried using the data conversion task and it didn't work and also tried cast and convert. Is this even possible or is there a way around it?



thanks,

View 14 Replies View Related

Problem Inserting Sql Query Into Database Float Datatype Field Using SQL Transaction

Aug 22, 2006

I have this problem of inserting my query into database field. My code is as of below.
The @AVERAGESCORE parameter is derived from
Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)
and the value returned is  (averagescore.toString("0.00"))
However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?
  Try
Dim i As Integer
For i = 0 To arraySql.Count - 1
myCommand = New SqlCommand
Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i)
myCommand.CommandText = sqlStr
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
With myCommand.Parameters

.Add(New SqlParameter("@AVERAGESCORE", consolidatedobjitem.getaveragescore))

End With
myCommand.ExecuteNonQuery()
Next
myTrans.Commit()
myConnection.Close()
Catch ex As Exception
Console.Write(ex.Message)
myTrans.Rollback()
myConnection.Close()
End Try 

View 7 Replies View Related

How To Convert Float To Varchar Or String..

Feb 12, 2007

I've three columns:
Length          Width            Height
1.5                  2.5              10
2                   3.7                19
 
in Query I want to display Like 1.5 X 2.5 X 10 (Length, Width, Height).....
 
???

View 1 Replies View Related

Convert Float To Numeric Error..... HELP!

Jan 23, 2002

I'm running the following statement...

The column is currently float (8). Need to convert to numeric. I've tried cast, convert. no go for either.

Any help on this would be greatly appreciated.

..... select ... convert(numeric(38,0), colname)


TIA,

Jeremy

View 1 Replies View Related

Convert Varchar Or Char To Float

Jan 4, 2006

Hello,
Is there a way to convert varchar or char to float?
Thank you

View 6 Replies View Related

Convert Varchar To Float / Decimal?

Jan 10, 2012

I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.

eg of values.

10.00
20.00
0.00
15.00

or could be missing when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error

Msg 8114, Level 16, State 5, Line 2

Error converting data type varchar to numeric.

View 2 Replies View Related

Convert Text To Float In SQL Statement

Feb 20, 2004

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.

View 6 Replies View Related

SQL 2012 :: Convert Date To Float

Apr 2, 2014

What actually happens when I convert date to float? Select convert(float,getdate(),112)

View 1 Replies View Related

T-SQL (SS2K8) :: Convert Varchar To Float

Feb 11, 2015

I have the following CASE statement:

SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]

It errors on the highlighted code.

Error converting data type nvarchar to float

the code should return :

<0.5
<1

I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error

Conversion failed when converting the varchar value '<' to data type int.

How I can concatenate the < with a float value?

View 5 Replies View Related

Problem Convert Varbinary To Float

Jul 20, 2005

Hello I have some problems with converting varbinary to float in T-SQLstored procedure. In my C# application i have table of structures withdouble type fields. Size of this table is variant. I have to send thistable to SP. Because of performance i want to send it only once andwhole. So I have to cut this varbinary blob in my Stored procedure toreceive structure field values. But I have problems with double fields- conversion from varbinary to float in T-SQL is not allowed. Pleasehalp me - thx for any advise.Maciej

View 1 Replies View Related

Transact SQL :: Convert Float To Money

Oct 6, 2015

I have a column type of float and How to convert it show like this

default value =39260.80 MY db use this ',' seperator instead of '.'
wanna convert 39,260

This code is working if using '.' seperator

SELECT  parsename(convert(varchar,CAST(floor('39260.80') AS MONEY),1),2)
not work using ',' seperator
SELECT  parsename(convert(varchar,CAST(floor('39260,80') AS MONEY),1),2)

View 12 Replies View Related

Convert Float To Decimal Errors

May 18, 2006

I'm trying to move records from a SQL table with a float column to a DB2 database that has the column defined as Decimal (8,2) It keeps crashing saying it has a type mismatch problem. I tried changing my source command to pass in the column already converted and it still crashes on this. I also tried doing a data conversion task to do the conversion and I still get the same error. Any ideas?

View 3 Replies View Related

T-SQL (SS2K8) :: Convert Float To A Phone Number

Apr 2, 2014

I seem to always get the "Fun Stuff" to try and figure out. I have an entire table that was pumped out of Oracle. I even hate saying that word!

There are a couple columns that are Float data type, and they are storing phone numbers as a Float data type. I am not able to CAST these into anything that is legible.

This is one of the values that I made up that look like some of the others.

9.72732e+009

View 2 Replies View Related

How To Convert Float To Timestamp In Single Select

Mar 18, 2015

how to convert float to timestamp in single select query..for exp. i have float as 1.251152515236 ,i want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00

View 6 Replies View Related

How To Convert A (varchar(21), Null) Variable To Float???

Dec 28, 2007

Hi there, I've a question regaarding datatype conversions... I can't convert the above mentioned datatype. I always get an error message that the conversion fails. Doesn't matter If convert or cast is used.

How would you convert the above mentioned variable into float???

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved