SQL Conversion failed when converting the nvarchar value '3.00' to data type int.

CipherIS
CipherIS used Ask the Experts™
on
Getting error:  Conversion failed when converting the nvarchar value '3.00' to data type int.

on Field LeadTime. I've tried Cast and Convet and both fail.
SELECT MPN, InternalPN, CONVERT(INT, LeadTimeMin) FROM AustinFullExportTemp WHERE MPN = 'xxxxx'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Try:
SELECT MPN, InternalPN, CAST(LeadTimeMin AS NUMERIC(10,2)) FROM AustinFullExportTemp WHERE MPN = 'xxxxx'

Open in new window

If use the above I get a value back of '3.00'
If the text value was '3.75' I'd get 3.75
You indicated that you want the int value which is 3 for '3.00' and 3 for '3.75'
If I were to change numeric(10,2) to numeric(10,0) the results would not be correct as it would give 3 for '3.00' and 4 for '3.75'

So, I would then change the code to:
SELECT MPN, InternalPN, CONVERT(INT,CAST(LeadTimeMin AS NUMERIC(10,2))) FROM AustinFullExportTemp WHERE MPN = 'xxxxx'

Open in new window


Someone else might have a cleaner way to accomplish this, but the above should work for you.

Author

Commented:
Thanks!!!
Top Expert 2008

Commented:
Also, just in case the value in the field is not always numeric text you could use
SELECT MPN, InternalPN, CASE WHEN ISNUMERIC(LeadTimeMin) = 1 THEN CONVERT(INT,CAST(LeadTimeMin AS NUMERIC(10,2))) ELSE 0 END FROM AustinFullExportTemp WHERE MPN = 'xxxxx'

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial