Hello all, I am having a lot of trouble maintaining decimal precision in the script below. Specifically, the statement: SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT)). This should be returning a large decimal value, but some of the values that are being updated into the Straights table keep showing up incorrectly rounded - sometimes as whole numbers. In one particular example, the statement above would become... 1-(POWER(.999,10201)). Note: 10201 is the result of @CURRENT - @LASTHIT. This should then equate to 0.99996306 as the answer to the formula. For some reason, when I open the Straights table, the result shown is 1.00000000. Please note that the result is loaded into the variable @SKIPPROB which is declared to be a Decimal(10,8). Also, the column in the straights table that this value is updated into is also set as Decimal(10,8). I am still very new to this, so am I overlooking something. I don't understand why the result is being rounded up to 1.00000000. Any help is appreciated. DECLARE @EXACT INTEGER, @CURRENT INTEGER, @LASTHIT DECIMAL(10,5), @AVGSKIP decimal(10,2), @MAXSKIP INTEGER, @1000AGO DECIMAL(10,4), @HITS1000 DECIMAL(10,4), @HITSLIFE DECIMAL(10,4), @SKIPPROB Decimal(10,8) SET @EXACT = 1000 WHILE @EXACT > 0 BEGIN SET @EXACT = @EXACT -1 SELECT @CURRENT = (SELECT MAX(GAME) FROM History) SELECT @LASTHIT = (SELECT MAX(GAME) FROM History Where EXACT = @EXACT) SELECT @AVGSKIP = (SELECT AVG(cast(exactskip as decimal(10,2))) FROM HISTORY WHERE EXACT = @EXACT) SELECT @MAXSKIP = (SELECT MAX(EXACTSKIP) FROM History WHERE EXACT = @EXACT) SELECT @1000AGO = (SELECT MAX(GAME) FROM History)-1000 SELECT @HITS1000 = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT AND GAME > @1000AGO) SELECT @HITSLIFE = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT) SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT)) UPDATE Straights SET [CURRENT SKIP] = @CURRENT - @LASTHIT, [AVG SKIP] = @AVGSKIP, [MAX SKIP] = @MAXSKIP, [TIMES DUE] = (@CURRENT - @LASTHIT)/1000, [FLIPS] = (@CURRENT - @LASTHIT)/692.8005, [HITS 1000] = @HITS1000, [PERCENT 1000] = (@HITS1000 / 1000)*100, [TOTAL HITS] = @HITSLIFE, [PERCENT LIFETIME] = (@HITSLIFE / @CURRENT)*100, [SKIP PROB] = @SKIPPROB WHERE EXACT = @EXACT; END
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
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!
Hi! I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?
I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)
Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.
We are having problems with rounding errors on large monetary calculations in sql server 6.5
The calculations include float fields (for volumes and unit of measure conversions in product movements). I was wondering if the float being "approximate" could be the problem.
IF it is, why would I want to store things as a float instead of a dec(28,14)? Is it faster to compute numbers stored as approximate binaries? Will we see a big performance hit if we switch some of the table`s field`s to decimals?
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
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?
Hi there I have two Databases in both databases are fields with float - no null If I am transfering data from one database to the other everything works well unless there is a comma in the field ( 0,99 or 123,456 )
"SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,EDVEingang ,EDVAusgang,InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & EDVEingang & "," & EDVAusgang & "," & ArtMenge & ")" " where EDVEingang and EDVAusgang are defined as float, no null
Then the programm stops with the following message: Within the INSERT-Procedure there are less columns then there are Contents in the Value-Clause.
I have to finish the programm until tomorrow morning and don't know what the problem is.
Hi!How do I do to make t-sql not rounding the result that i returned?For example:0.9616458*60 = 57,698748 (in any calculator)while following:--------------------------------declare @a floatdeclare @b intset @a=0.9616458set @b=60print @a*@b---------------------------------will show :57.6987How do I do to make MSSQL to show me the value whothout rounding it?Thanks!
select convert(float,'1.2334e+006')1233400.0select convert(decimal(20,2),'1.2334e+006')Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.Is there any way around?Is there any set options? I tried arithabort or arithignore and theydon't work.Thanks.
HI,I WANT THIS TO PRODUCE EXACT RESULT. (IN SQL SERVER)SELECT (23 / 233) * 100THE ANSWER SHOULD COME 9.871244635 OR 9.87BUT IT RETURNS 0.I WANT THE PERCENTAGE.HOW TO HANDLE THIS KIND OF PROBLEM.IS THERE ANY SET COMMAND FOR IT?THANKST.S.NEGIJoin Bytes!
I am keep getting an arithmetic overflow converting float to type numeric when running a script that looks something like this.
insert into table1 ( column1 ) select column2 from source server.
column1 is a numeric (28,8) and column2 is float. there are about 2000000 records in column2, and I know that when I tried just copying the top 1000000 wasn't a problem.
does anyone know what could be causing this problem???
*it's not because the data in column2 is out of range.
I'm using c# 2.0. I have a datareader that reads an invoice line item from a table in my SQL Server database. The UnitPrice field is a Money field in SQL server. At the same time I have an invoice class in my application that has a UnitPrice property which is a float. How do I convert the SQLMoney to a float using my datareader? Right now I have: cm.CommandText = "SELECT ItemID, Quantity, UnitPrice, Discount FROM tblInvoiceLineItems"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { LineItem li = new LineItem(); li.UnitPrice = (float)(double)dr.GetFloat(3); // cast error here. lineItems.Add(li); } }
hi im using a stored procedure to insert float numbers in a data base....the parameter i send to the stored procedure is a float but every time i use it, it inserts a number with a lot of extra numbers i mean if i try to insert 5.3 it inserts 5.30000019073486 any idea why this is happening??? thak you
Hi All, What is the difference betwen real and float datatype of SQL server. Please one exapmle for each. The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters. Thanks Abdul
Hi I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.
I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.
But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.
In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:
//properties public float RangeFrom { get { return cvintRangeFrom; } set { cvintRangeFrom = value; } }
public float RangeTo { get { return cvintRangeTo; } set { cvintRangeTo = value; } }
// some code deleted for readability....
public int AddOption() { string cvstrSpName = "addOption"; SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true); //lines deleted for readability... //check if the optional fields have a value associated with them. if not, assign dbnull.value. cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value); cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value); //lines deleted for readability... SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams); return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value)); }
I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.
The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!
objQuestion.AddOption(); //txtrangefrom.Text=""; on the next line I test the value in the business object... txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!! //txtrangeto.Text=""; txtrangeto.Text=objQuestion.RangeTo.ToString();
So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser... Any help or ideas will be greatly appreciated...
I am using SQL Server 7.0. I create a table with one field..type of float. Using SQL Server Query Analyzer: INSERT INTO MyTable(MyField) VALUES (4.9) INSERT INTO MyTable(MyField) VALUES (Round(4.9,2))
SELECT * FROM MyTable
Result = 4.9000000000000004
This is a basic example of a problem I am having in another table with the same float field that I am using to store money in. I don't want to use the money field as the BDE from Borland has some issues with money fields. Any suggestions? Thanks in advance.
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
We are using a GL package called Solomon. It uses SQL Server 7.0 for it's database. I want to create a data warehouse using this data as the source. The package uses the float data type for dollar amounts. The dollar amounts in the data have either no numbers, 1 number, or 2 numbers after the decimal point. Is the float data type the best one to use in my data warehouse for dollars and cents, or should I try using the monetary or decimal (precision 2) data type? Which type uses the most storage?
I've got a float data type in a table. I imported data from a csv file. One value was 0.5195, but when I use the value in a calculation or select it using the Query Analyser, I'm getting a value of 0.51949999999999996. Is there any way around this, it's a real pain?
i'm get some strange behaviour with the money and float type. with a calculator divide 70 by 47 and multiply by 40 (70 / 47 * 40) and you get 59.5744 (rounded to nearest 4 decimal places). now try this in query analyser with the 70 as a money type and the result as a float:
I define one of my columns as FLOAT. Some of the values are negative and positive with precision 8 or 12. When I run updates against that column and then check the data all values are 0.
I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Good morning, I'm having trouble resolving an issue with the results of a column defined as a float. The problem is that the results returned on a development server are different from the results returned on the production server. Both servers are running sql2000 service pack 3, and both servers are on Windows 2000 service pack 3. When running the statement "select factor from csm_security where cusip = '07383FJB5' ", the results differ. The dev server returns 0.91531495529999995, but the prod server returns 0.91531496000000001. The result from the development server is what we expect, and require on the production server. I've compared all configuration settings, and cannot see a difference. Any ideas as to why this may be happening? Thanks for any insight.