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
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?
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?
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?
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???
So my data is delivered as numeric(9,2)...like 100.00. I have to extract that data and store it as a varchar 0 filled without the decimal place...like 0000010000///I tried the following and it did not work...
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!
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?
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.
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
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
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
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...
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.
