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
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
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!
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
Hi,
how to convert nvarchar datatype to float?
Regards
Prashant
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.
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
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
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
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 RelatedI 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
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!
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
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
Hi folks, need ur help.
I want to convert strings from a textfile and convert em to appropriate datatype for the destination columns, eg if i have a source value in string 11111 and the destination column is MONEY. I want to use CONVERT(functionretruningdatatype,'11111') in my insert statement.
Any guidance!
Howdy!
I want get DataType of Column in Table
ex: nvarchar, varchar, etc...
Help me !!
I've changed the width of a column from 128 to 64. How to EM shows 64, while sysobjects and sp_help show 128 ?? Even after disconnecting and re-connecting (just to be extra sure).
View 2 Replies View RelatedHello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank you
Hi,
I have a data flow task where I have mutiple columns (of different data types) , I want to create a single column using all these columns in XML format and load it into a table with a column of datatype XML. Can I accomplish this using Derived column tranformation?
Thanks
hi ,How can I find sql Datatype (like NVARCHAR , DESIMAL & .. ) and size of columns and also is it Identity or not in a Table of SQl server?
View 4 Replies View RelatedHello,How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV). I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data. EX: "$2,000" >> 2000The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?
View 4 Replies View RelatedHi all
A table consists of a column with name "Createddate" with datatype smalldatetime.
defaultly iam binding getdate() to Createddateso it is storing in tthe below format --
Mar 12 2008 11:38AM
while retreving iam mentioning...
select * from tbl where convert(varchar,createddate,101) = '03/12/2008'
it is showing no records ..even though records r there with --march 12 2008.
plz do needful in this senario.
thanks & regards
kanth
Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.
Hi guys,
Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????
Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.
Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.
MY PROBLEM:
~~~~~~~~~~~~~
'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS.
I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.
IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.
Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-Sudhakar
I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'
I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?
MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable
USING (SELECT source_code, name, addr1, city, zip FROM
[D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
[code]...
I am having Test table with (id int , DataDescription xml) . I need to read the values from DataDescription column and insert into one table.
View 2 Replies View RelatedHi,
i am trying to load output of count(X) and sum(salesamt) into the same column. if iam using transformation data task what datatype should i be converting the two outputs to accomidate result as
10.00 --count
234.00 --saleamt
22.00 --count
1000.00 --saleamt
Hello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.
View 8 Replies View RelatedHere is my expression in a derived component:
"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO
PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.
The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.
However, the derived component failed everytime giving me
Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.
Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?
1) it is in the same filegroup as the rest of the table?
2) it is stored in a filegroup separately from the rest of the table (I think this is allowed)?