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
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.
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