Convert error on T-SQL statement

1

I use the convert function within T-SQL to move regular data (ie all defined as nvarchar) into a SQL table that has the fields explicitly defined (ie float, int etc).

I regularly do this for amount fields, however when attempting to convert a quantity field, I am getting a conversion error. I see no reason at all for this error. The input data looks all fine.

Has anyone encountered this, and also found a solution to deal with it?

Happy to share my code and data if that helps

Kind regards
ash

2
  1. what is the code?
  2. what is the problem data?
  3. (most importantly) what is the error message?
3

The Code as below, the offending statement higjlighted

UPDATE B_ServiceLabour
SET
YearPeriod=rtrim(ltrim(YearPeriod)),
OrderNumber=rtrim(ltrim(OrderNumber)),
LineNumber=rtrim(ltrim(LineNumber)),
ActivityNo=rtrim(ltrim(ActivityNo))
Import_Step6:
-- delete existing data
TRUNCATE TABLE [Jedox_prod].[dbo].[F_ServiceOrderCostValues]
Import_Step7:
-- Insert new data
INSERT INTO F_ServiceOrderCostValues
([Period],
[OrderNumber],
[LineNumber],
[ActivityNo],
[Reference1],
[Reference2],
[CostComp],
[Amount],
[Unit],
[CostPrice],
[CostCur],
[CostCtrct],
[CostWarr],
[CostInvc],
[CostOth],
[SalesPrice],
[SalesCur],
[SalesCtrct],
[SalesWarr],
[SalesInvc],
[SalesOth],
[Company],
[DataType],
[InvoiceNo],
[InvoiceDate],
[AmountHC],
[USD],
[USD_PlanRate],
[EUR],
[GBP],
[NewRecord])
SELECT
B_ServiceLabour.YearPeriod,
B_ServiceLabour.OrderNumber,
B_ServiceLabour.LineNumber,
B_ServiceLabour.ActivityNo,
B_ServiceLabour.Reference1,
B_ServiceLabour.Reference2,
B_ServiceLabour.CostComp,
convert(float,B_ServiceLabour.Quantity),
B_ServiceLabour.Unit,
convert(float,B_ServiceLabour.CostPrice),
B_ServiceLabour.CostCurrency,
convert(float,B_ServiceLabour.CostCtrct),
convert(float,B_ServiceLabour.CostWarr),
convert(float,B_ServiceLabour.CostInvc),
convert(float,B_ServiceLabour.CostOth),
convert(float,B_ServiceLabour.SalesPrice),
B_ServiceLabour.SalesCurrency,
convert(float,B_ServiceLabour.SalesCtrct),
convert(float,B_ServiceLabour.SalesWarr),
convert(float,B_ServiceLabour.SalesInvc),
convert(float,B_ServiceLabour.SalesOth),
B_ServiceLabour.Company,
B_ServiceLabour.DataType,
B_ServiceLabour.InvoiceNo,
(select
case when InvoiceDate IS NULL THEN ''
else convert(datetime,InvoiceDate,3)
end),
0,0,0,0,0,-1
FROM B_ServiceLabour
END

The error is
Msg 8114, Level 16, State 5, Procedure P_Service OrderCostValues_F, Line 18
Error converting data type nvarchar to float.

many thanks

4

Most likely there is a non numeric value in B_ServiceLabour.Quantity
but I would check your other data also.

Try something like

SELECT Quantity 
FROM B_ServiceLabour 
WHERE ISNUMERIC(Quantity) <> 1;

As a first step.

5

Hello,
Yes, the issue lay in a couple of columns with invalid data.
Many thanks for pointing me in the right direction

/ash

6

Our data is so bad that is the first place we look.:slight_smile:

1 Like