I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
I want to concatenate the column_names with the respective values.
Create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'), (2,'SAM','CANADA'), (3,'HO','CHINA'), (4,'RAM','INDIA')
select * from #test1
I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values 1,NAME-john-COUNTRY-USA 2,NAME-SAM-COUNTRY-CANADA 3,NAME-HO-COUNTRY-CHINA 4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic
select ID, (a+'-'+B) as Value from ( select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1
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); } }
I have a problem converting a float filed into nvarchar. select cast(sold as varchar(50)) where sold=431597.15 results in 431597 and is ignoring always my decimals. Do you have any idea how to fix this? ty
Hello all,I need to return back to TSQL two numeric values from anExtended Stored Procedure developed in C. As result my Cdll program produces two float values, the TSQL side expectsto have exactly: numeric(10, 5) and numeric.Given that the structure to fill-up is DBNUMERIC definedas:typedef struct dbnumeric // Numeric (and decimal){BYTE precision; // PrecisionBYTE scale; // ScaleBYTE sign; // 1 = Positive, 0 = NegativeBYTE val[MAXNUMERICLEN]; // Padded little endian value} DBNUMERIC;I do not have any clue how to convert the float C datatypeto DBNUMERIC, specifically how to convert the float to anarray of bytes "BYTE val[MAXNUMERICLEN]".Thanks in advance,Best Regards,Giovanni
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?
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.
As i need to concatenate column value with dependent belongings just like if i pass canon categoryid in where conidition,so desired result should be canon->Dslr Lens->Digital camera->Electronics because As 'canon' belongs to categoryid- 40(DSLR Lens) then 40 belongs to categoryid-39(Digital camera) then 39 belongs to categoryid-35(Electronics) in single select statement...
I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;
[code="CASE WHEN isdate( arc.UserField1 )= 1 then CAST( arc.UserField1 as DateTime ) ELSE CAST( '01/01/1900' as Datetime ) END"] [/code]
The results are ;
Date Result 29/09/08 1900-01-01 00:00:00.000 09/06/15 2015-09-06 00:00:00.000 18/03/13 1900-01-01 00:00:00.000 09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?
declare @localtab INT SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename))) Print @localtab Print @localtab
----
Msg 245, Level 16, State 1, Line 8 Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct (select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.
My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.
select * from SourceTable -- produces 22461 records
--step 1: first, get row number of child records in each parent
SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num INTO #RelationshipTmp FROM SourceTable;
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.
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 can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
I'm inserting a decimal value into my db but it keeps round it of to a single figure. Its the result of time between two times. ie between 1:00 and 2:30 equals 2.5. But it ends up as 2 in the db even though the db field is also decimal. I know by looking at the watch in the bugger that its actually a decimal value just prior to insertion which only leads me to suspect that sql server is doing the rounding of, maybe this is the default behaviour but i need it to store the values as decimal.
I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.There is a textbox for the user to enter the price. A stored procedure is used to insert the line item info to the data base table. The field in the table is formatted as datatype decimal. Below is the stored procedure and the code that adds the value to the parameter. Any suggestions on where I am going wrong?<Code>CREATE PROCEDURE [sp_insert_CartDetail] ( @ReferenceNum [int], @Item [varchar](26), @Desc1 [varchar](27), @Desc2 [varchar](20), @Desc3 [varchar](30), @Desc4 [varchar](30), @Note [text], @Quantity [int], @DateOrdered [datetime], @SalesPrice [decimal], @DistCost [decimal], @SalesTaxable [smallint], @RequiredDate [datetime], @User1 [varchar](12), @User2 [varchar](12), @User3 [varchar](12), @User4 [varchar](12), @User5 [varchar](12), @User6 [varchar](12)) AS INSERT INTO [ToolCrib].[dbo].[CartDetail] ( [ReferenceNum], [Item], [Desc1], [Desc2], [Desc3], [Desc4], [Note], [Quantity], [DateOrdered], [SalesPrice], [DistCost], [SalesTaxable], [RequiredDate], [User1], [User2], [User3], [User4], [User5], [User6]) VALUES ( @ReferenceNum, @Item, @Desc1, @Desc2, @Desc3, @Desc4, @Note, @Quantity, @DateOrdered, @SalesPrice, @DistCost, @SalesTaxable, @RequiredDate, @User1, @User2, @User3, @User4, @User5, @User6)SqlCmdInsetCartDetail.Parameters("@SalesPrice").Value = CType(txtCost.Text, Decimal) </code>