Am I missing something or is there something odd with float data types. i know that float is not the most precise definition but i came across something really odd today.
first let me define the scenario.
this is sql server 2005, standard edition build 3042.
I have a table defined as
CREATE TABLE [dbo].[ASSET](
[Property_Num] [numeric](10, 0) NOT NULL,
[Accrual_Factor_Val] [float] NULL
)
the accrual_factor_val was updated to a value of 0.00005 then the web service failed because the proc returned 5E-05!
i opened the table, and discovered this is the stored value. is this correct?
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.
I'm trying to find a way to format a FLOAT variable into a varchar inSQL Server 2000 but using CAST/CONVERT I can only get scientificnotation i.e. 1e+006 instead of 1000000 which isn't really what Iwanted.Preferably the varchar would display the number to 2 decimal placesbut I'd settle for integers only as this conversion isn't businesscritical and is a nice to have for background information.Casting to MONEY or NUMERIC before converting to a varchar works finefor most cases but of course runs the risk of arithmetic overflow ifthe FLOAT value is too precise for MONEY/NUMERIC to handle. If anyoneknows of an easy way to test whether overflow will occur and thereforeto know not to convert it then that would be an option.I appreciate SQL Server isn't great at formatting and it would be fareasier in the client code but code this is being performed as adescription of a very simple calculation in a trigger, all stored tothe database on the server side so there's no opportunity for clientintervention.Example code:declare @testFloat floatselect @testFloat = 1000000.12select convert(varchar(100),@testFloat) -- gives 1e+006select cast(@testFloat as varchar(100)) -- gives 1e+006select convert(varchar(100),cast(@testFloat as money)) -- gives1000000.12select @testFloat = 12345678905345633453453624453453524.123select convert(varchar(100),cast(@testFloat as money)) -- givesarithmetic overflow errorselect convert(varchar(100),cast(@testFloat as numeric)) -- givesarithmetic overflow errorAny suggestions welcome...CheersDave
I've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that?
HiWe've got some numbers stored as Reals which are returning values inscientific notation that we need rounded down to 3 digits to the rightof the decimal.ie 8.7499999E-2 needs to return 8.75.Round, cast, convert, formatnumber in the dts package all fail.Help!Thanks Moe
SELECT membername, outputval case when choice = 0 then outputval else null end as outputval from MyDatabase group by membername, outputval
how to format outputval: if outputval < 40000 format outputval as: 5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc. if outputval >= 40000 format it as a scientific.
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?
Hi, I am new to SQL 2005. I have to design schema for scientific data warehouse. Data is available in 2 or more flat data files recorded at 1 sec interval. At Least 2 of the data files have 100+ columns. I am inclined to create a table per data file type. I want to know If this is correct/optimal for me to do?
I don't think I can create normalize tables based on the headers in these Data files.
Primary Objective of this data warehouse is make it available for reporting services and Analysis Services.
Hi, I am new to SQL 2005. I have to design schema for scientific data warehouse. Data is available in 2 or more flat data files recorded at 1 sec interval. At Least 2 of the data files have 100+ columns. I am inclined to create a table per data file type. I want to know If this is correct/optimal for me to do?
I don't think I can create normalize tables based on the headers in these Data files.
Primary Objective of this data warehouse is make it available for reporting services and Analysis Services.
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
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!
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.
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?
Hi all, I have declared a field with datatype as float. When I enter value with two precision it chooses to round off to lower value and insert into the database.
I am losing precision in this case.
I want to insert 4.56. It inserts the way shown and hence all my further calculations go haywire.
Is SQL server designed to store float like this or Am I doing soemting wrong ???
I am using SQL Server 2000, VS 2003 I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error. while (dr.Read()){ Education e = new Education(); e.EducationId = dr.GetInt32(0); e.Country = dr.GetInt32(1); e.InstitutionName = dr.GetString(2); e.Grade = dr.GetString(3); e.CGPA = dr.GetFloat(4); // ERROR HERE e.Percentage = dr.GetFloat(5); e.PassingYear = dr.GetString(6);} where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
Hopefully ur guys can help me. I declare data type as float in price table. So the problem here is, whenever i enter a float number and move to the second row to enter a new number. The first number in the first row will automatically change to integer, without no reason. For example "2.3" will become "25".
1. In c++ (or any other language) I'm copying bit by bit to a doublevariable dob_var.2. If I copy dob_var to dob_var2 which is also a double I do not loseinformation.3. This implies that the same would be when copying to a data base, butwhen I write dob_var to an sql server float field (same size as adouble variable = 64 bit) it sometimes loses information. e.g. whenvalue=1E-320.It is true that SQL float is valid until 1E-308 but so is a C++ double.Any comments?
I am connecting to MSSQL through ODBC using regular SQL commands (SQLAllocHandle, SQLConnect, SQLFetch). Sometimes when I retrieve float data from the server it gets corrupted. For instance, instead of 59.457443 I will actually get 59.45744299999998. The value that is stored in the database is 59.457443 and it is stored as a float. The code I use to retrieve the data worked OK with an Access database, the problem only appeared after switching to MSSQL. Does anybody know if it's a configuration issue? Any help would be greatly appreciated.
I'm not a professional dba or dbd, but I'm proficient in the basics ofdatabase design and sql. I want to create a database of mathdefinitions, and I'm wondering how one would go about creating adatabase that contains mathematical notation (and I'm not just talkingabout basic symbols where I could get away with ascii code). I needto be able to insert a wide variety of mathematical expressions, fromfractions to integrals, into fields (just like you can enter in-linemath symbols in MS Word using equation editor). I have no clue how togo about this. Is it a matter of developing certain programmingskills/languages? Would such a capabliltiy be proprietary (dbms-specific)? Is it possible at all? Any help would be appreciated.Thank you.
hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...
below is my table ClimateData
quote:USE [PVMC Database] GO /****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ClimateData]( [Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()), [Latitude] [float] NULL, [Longitude] [float] NULL, [Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL, [PV_application_id] [uniqueidentifier] NULL, CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED ( [Climate_application_id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO USE [PVMC Database] GO ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id]) REFERENCES [dbo].[Photovoltaic] ([PV_application_id]) ON UPDATE CASCADE ON DELETE CASCADE
Below is photovoltaic table
quote:USE [PVMC Database] GO /****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Photovoltaic]( [PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()), [PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_nominal_power] [float] NULL, [PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL, [PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL, [PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()), [PV_site_photo] [varbinary](max) NULL, [PV_peak_nominal_rating] [float] NULL, [User_application_id] [uniqueidentifier] NULL, [Org_application_id] [uniqueidentifier] NULL, CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED ( [PV_application_id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO USE [PVMC Database] GO ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id]) REFERENCES [dbo].[OrganizationDetail] ([Org_application_id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id]) REFERENCES [dbo].[Users] ([User_application_id]) ON UPDATE CASCADE ON DELETE CASCADE
Below also my command for instead of trigger
quote:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as BEGIN
insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module, PV_mounting) select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system, inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site and inserted.PV_state = Photovoltaic.PV_state and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power and inserted.PV_nominal_power = Photovoltaic.PV_module and inserted.PV_nominal_power = Photovoltaic.PV_mounting where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/
insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id) select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type, Photovoltaic.PV_application_id from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site left join ClimateData on inserted.Latitude = ClimateData.Latitude and inserted.Longitude = ClimateData.Longitude and inserted.Altitude = ClimateData.Altitude and inserted.Climate_type = ClimateData.Climate_type where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/
END -- trigger def
and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert
Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6 Error converting data type varchar to float.
quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData] AS SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power, dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude, dbo.ClimateData.Climate_type FROM dbo.ClimateData INNER JOIN dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id
below is my instead of trigger command...
quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as BEGIN
insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module, PV_mounting) select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system, inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site and inserted.PV_state = Photovoltaic.PV_state and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power and inserted.PV_nominal_power = Photovoltaic.PV_module and inserted.PV_nominal_power = Photovoltaic.PV_mounting where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/
insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id) select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type, Photovoltaic.PV_application_id from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site left join ClimateData on inserted.Latitude = ClimateData.Latitude and inserted.Longitude = ClimateData.Longitude and inserted.Altitude = ClimateData.Altitude and inserted.Climate_type = ClimateData.Climate_type where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/
END -- trigger def
this is my commad insert values using instead of trigger that i've created...
after execute this commad, i got this error.. quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6 Error converting data type varchar to float.
I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?
I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better.Â
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions: 1. Why it is taking 1st 2 Charecters? 2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?Â
Hello, I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column. The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file. Thanks
In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.
SELECT QA_AutoID, AccessVal, SQLVal ,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat ,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat FROM QA WHERE QA_AutoID in ( SELECT TOP 1 QA_AutoID FROM QA WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1 ) --AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0) ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC ,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC
Here is the output with the comparison commented out...
Here's what I get with the comparison line activated:
I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.
In Reporting Services, is it possible to display a field in Engineering Notation or Scientific notation? I guess can do it by adding a format code, can somebody tell me the format for those notations.
