question

ash1807 avatar image
ash1807 asked

Inserting Nvarchar into float datatype

Not much of an expert in SQL. I am getting the dreaded "Msg 8114, Level 16, State 5, Line 1" error when trying to insert a string field, and converting to float in the same instance. I have no nulls in my input and the format of the input is ...nnnn.nn The complete INSERT as below@ INSERT INTO F_ContractAcq (ContractNo, ContractName, Reference, SalesRep, UID, Acq_Date, Start_Date, End_Date, Termlength, TermType, ContCur, AcqInRpt, PostedInRpt, BacklogInRpt, HomeCur, AcqInHome, PostedInHome, BacklogInHome, Region ) SELECT LEFT(ContractNo, 9) AS ContractNo, LEFT(ContractName, 50) AS ContractName, LEFT(Reference, 50) AS Reference, LEFT(SalesRep, 9) AS SalesRep, LEFT(UID, 6) AS UID, CONVERT(DATETIME, Acq_Date, 5) AS Acq_Date, CONVERT(DATETIME, Start_Date, 5) AS Start_Date, CONVERT(DATETIME, End_Date, 5) AS End_Date, LEFT(TermLength, 3) AS Termlength, LEFT(TermType, 8) AS TermType, LEFT(ContCur, 3) AS ContCur, CONVERT(FLOAT, REPLACE(AcqInRpt, '.', '')) AS AcqInRpt, CONVERT(FLOAT, REPLACE(PostedInRpt, '.', '')) AS PostedInRpt, CONVERT(FLOAT, REPLACE(BacklogInRpt, '.', '')) AS BacklogInRpt, LEFT(HomeCur, 3) AS HomeCur,-- CONVERT(FLOAT, REPLACE(AcqInHome, '.', '')) AS AcqInHome,-- CONVERT(FLOAT, REPLACE(PostedInHome, '.', '')) AS PostedInHome, CONVERT(FLOAT, REPLACE(BacklogInHome, '.', '')) AS BacklogInHome, LEFT(Region, 3) AS Region FROM B_ContractAcq WHERE ContractNo NOT IN (SELECT ContractNo FROM F_ContractAcq); /Ash
sqldatatypesnvarchar
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The reason for this conversion is that I already have an input sql table where every field is defined as nvarchar(50). My insert is re-formatting the data to the new table to be used for a BI application read. All the conversion data (amounts) are manditorily filled up but does contain decimal point, thus the "replace" Mant thanks ash
0 Likes 0 ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
The value you're passing in must not contain only numbers, so you're getting an error. It's a best practice to treat all values as the data types that they need to be. Storing strings as numbers or numbers as strings inevitably leads to problems.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

manoj 2 avatar image
manoj 2 answered
it is giving string to float conversion error whenever you are converting in your statement. It wont allow to convert. I am not understanding what is need to convert string to Float. Can you please elaborate more on this. Thanks Manoj Bhadiyadra
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.