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

Question

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

What is that DATETIME(23) datatype?? Is it just a silly typo and you really mean DATETIME2(3) ?? Also: when using dates in string literal form, I'd always recommend to use the ISO-8601 format YYYYMMDD (no dashes or anything) to be independent of any language & regional settings in your SQL Server — marc_s
– marc_s, Commented Jan 3, 2019 at 18:28

Thom A · Accepted Answer · 2019-01-03 18:26:46Z

5

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

answered Jan 3, 2019 at 18:26

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reza · Accepted Answer · 2019-01-03 18:26:15Z

1

try this

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

answered Jan 3, 2019 at 18:26

Reza

20.1k18 gold badges102 silver badges177 bronze badges

7 Comments

Mark Foley Over a year ago

That was a good idea adding the column names, but "insert into hprs.dbo.OHPRSrates (effdate,ratetype,rate) values ('01/01/2019','D',0.02642000)" does not work. Same error. I also tried converting the date value to datetime, just in case that was the problem, but that didn't help. Any other ideas? This query has actually been running for years, so I don't know what's different.

Thom A Over a year ago

Considering this is the same SQL as in my answe,r this implies there's more at hand, @MarkFoley . I suspect Martin Smith is right in the comment under your question "Any Triggers?"

Mark Foley Over a year ago

Ah! I missed Martin Smith's comment. Of course! I checked the insert trigger and yes, it was catenating a varchar with the numeric rate to log to a change record. I changed that to catenating the varchar value with convert(varchar,rate), and the insert worked! Thanks a for the help!

Mark Foley Over a year ago

BTW - this is my first stackoverflow post and I don't see how to mark an answer as accepted or a question as solved. I find nothing to 'click'

Reza Over a year ago

@MarkFoley Sorry for late response, you can upvote helpful answers (no matter how many) also you can mark it as answer below the upvote/downvote button

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related