I had database with "-1.#IND" (Indefinite /infinite) values in float columns.
Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.
I want to insert this value to replicate the issue..just for testing.I am wondring how my database got these values.
Thanks in Advance!
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
We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).
is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.
Hi,
I have a float column with Allow Nulls= false
Is it normal that when I try to set the column to 0, i get the error:
Cannot insert duplicate key in object
Please note that the column is part of a primary key and that there is no record with the value of the column 0 in the table in question.
Thank you.
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY
WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!!
//Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice.
//
// This calculation is not being done correctly.
//
// Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
-Rob Calhoun
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
Hi,
I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is
varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...
but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...
what can i do to rectify the problem...
regards
Karen
I have a flat file from which I am attempting to import a column that contains either float numbers or " "(single blank).
I get the following Report:
quote:
- Executing (Error)
Messages
* Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "ADR_SH_PER_ADR " returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
* Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ADR_SH_PER_ADR " (438)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ADR_SH_PER_ADR " (438)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
* Error 0xc0202092: Data Flow Task: An error occurred while processing file "F:WorkValMaster_Reference_Databasehs_1.txt" on data row 2.
(SQL Server Import and Export Wizard)
* Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - hs_1_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
* Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
* Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)
* Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
Now, the strange thing is, as soon as I import the same column from an Excel file in which, for simplicity of "text to Excel" transfer I have all the columns defined as "text"(I have 170 columns), the import works just fine. The Excel file is just a straight out import into Excel of the flat file.
The only difference I see between the flat file and the Excel file is that an empty value in the flat file contains a single blank, while an empty "cell" in Excel contains nothing(cursor doesn't go to the right after clicking inside the cell).
By the way, the column in the SQL table is nullable, which is why I thought there should be no issues from an import value containing blanks exclusively.
Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?
Thanks for any thoughts,
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?
Regards,
Sam
Hello everyone,
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.
Thanks,
Randy
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?
Thanks in advance.
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
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!
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.
If anybody has an idea, please let me know.
regards
Reiner
When should I choose decimal over float and vice versa?
Mike B
Hi Everyone,
here is a simple SP
Create procedure test
@input float
as
Begin
return @input
ENd
If I execute the following
declare @output float
exec @output=test 1.12121
select @output
why I got 1 not 1.12121?
any idea? How to get the correct result?
Thank you in advance
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!
View 16 Replies View Relatedselect 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.
View 2 Replies View RelatedHI,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!
View 1 Replies View RelatedHi,Just wonder whyPRINT CAST(0.0573542567654 AS float)will give the rounded reult0.0573543rather than the original number?"float" should be 'big' enough to hold numbers that have even moredecimal places. How come it round up at the 7 decimal place?since I need to do some calculations with accumulated values. Therounded figure will cause significant error after a number of opertions.Are there any way to work around it?thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related
I had database with "-1.#IND" (Indefinite /infinite) values in float columns.
Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.
I want to insert this value to replicate the issue..just for testing. I am also wondring how my database got these values.
Thanks in Advance!
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.
thank you
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:
if (txtrangefrom.Text != "") {
objQuestion.RangeFrom=float.Parse(txtrangefrom.Text);
objQuestion.RangeTo=float.Parse(txtrangeto.Text);
}
else {
objQuestion.RangeFrom=Convert.ToSingle(null);
objQuestion.RangeTo=Convert.ToSingle(null);
}
And this is what my Business object look like. It sets up the parameters and calls the Data Access Application Block to create an entry in my table:
// fieldslist
float cvintRangeFrom;
float cvintRangeTo;
//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?
View 2 Replies View RelatedI'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?
Thanks in advance.
hi,
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:
declare @price money
declare @result float
select @price = 70
select @result = @price / 47 * 40
Print 'result = ' + str(@result,18,4)
now this will print out 59.5720
am i missing something ?
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.
Any idea why?? I appreciate it.
Thanks,
David