Conversion failed when converting the nvarchar value to data type int on insert

Question

Conversion failed when converting the nvarchar value to data type int on insert

Weegee71

SSChasing Mays

Points: 604
More actions
August 31, 2023 at 11:00 am

#4276724
I have a table with an Int column.. this is being loaded with a JSON file...containing various values
One of this is a numerical value which i have found a bug
My code as follows:
```
DROP TABLE IF EXISTS [#ReturnItemsCalibration_Points]

CREATE TABLE [#ReturnItemsCalibration_Points](
[ReturnItemsCalibration_PointsID] [int] IDENTITY(1,1) NOT NULL,
[ReturnItemsCalibration_ChannelsID] [int] NOT NULL,
[Points] [int] NULL
)

insert into [#ReturnItemsCalibration_Points] (ReturnItemsCalibration_ChannelsID,Points) 
Values
(395 , '84.99')
```
This statement causes the following error :
Msg 245, Level 16, State 1, Line 42
Conversion failed when converting the nvarchar value '84.99' to data type int.
How do i get around this error?
Should i round the value down or up to go in?
I tried running this
```
insert into ReturnItemsCalibration_Points (ReturnItemsCalibration_ChannelsID,Points) 
Values
(395 , CAST('84.99' AS NVARCHAR(10)) )
```
But get the same error

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

84.99 is not an int, no matter what you do to that value it will never be an int, unless you round it, but "should" you be rounding it? What is the business logic? Why was the value 84.99 originally and not 85?

You should declare the right datatype decimal would be the best bet here to store the value supplied correctly.

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Hi Ant

The data is coming from a website with text entry , so people can enter 84.99 or 85...

Yes I am thinking of changing the datatype to Decimal but would have do an impact analysis of this

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

Decide what you want to happen in these cases ... how to 'get around it' will depend on your desired outcome.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Business logic needs to dictate what would happen here.

Also what form of validation happens on the front end?

Does it parse for numeric characters only and return errors if someone puts in an Alpha character, otherwise whats stopping people entering an AlphaNumeric string, then you'll need to store it as varchar/nvarchar and not decimal.

We cannot tell you what to do, the business need to tell you what to do and how it needs to be handled and how you get out of it etc.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 5

Wrap the value in a: FLOOR, CEILING or ROUND(,0) function, depending on how you want to handle the decimal part of the value.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".