CODEWITHSUNDEEP
sqlsql-servert-sql
Mark Foley
Mark Foley

Reputation: 21

Error converting data type varchar to numeric - but with numeric value

I have the following table in SQL Server 2014:

effDate  DATETIME(23) NOT NULL  
rateType CHAR(2) NOT NULL  
rate     DECIMAL(10, 8) NOT NULL

When I run the following query, I get an error message:

Select Failed: 8114 Error converting data type varchar to numeric.

My query:

insert into hprs.dbo.OHPRSrates 
values ('01/01/2019', 'D', 0.02642000)

Why? The only numeric column in this row is rate and the insert value is clearly numeric

Upvotes: 1

Views: 919

Answers (2)

Thom A
Thom A

Reputation: 95544

I suspect those columns are not in the order you believe they are in. When inserting, you should really declare the columns you're inserting into. Does this, by any chance, work?

INSERT INTO hprs.dbo.OHPRSrates (effDate,rateType,rate)
VALUES ('01/01/2019','D',0.02642000);

Also, however, I would recommend using a unambiguous date format, such as yyyyMMdd. Thus, for your date, '20190101'. A value of 01/01/2019 is 01 January what ever way you read it, however, is 01/02/2019 02 January or 01 February? The answer depends on your language (settings).

Upvotes: 5

Reza
Reza

Reputation: 19843

try this

insert into hprs.dbo.OHPRSrates 
(effDate, rateType, rate) values ('01/01/2019','D',0.02642000)

Upvotes: 1

Related Questions