Error converting data type varchar to numeric

Question

Error converting data type varchar to numeric

OldSageMonkey

SSC Veteran

Points: 265
More actions
July 22, 2013 at 12:05 pm

#395180

Hi All,
Im not the greatest at SQL but Im getting better. I do not understand why I get the following error in my simple select query. I get the (Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.)

SELECT
ed.[Employee Name],
bd.SORTUSER_EMPID,
bd.SORTDTTM
FROM
[BOXES].[dbo].[BOXDETAILS_ORL] bd
JOIN
[Employee].[dbo].[Employee Data] ed
ON
bd.SORTUSER_EMPID = ed.[Employee ID]
WHERE
SORTDTTM >= dateadd(day,-30,getdate())

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

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

calvo SSChampion Points: 12930 More actions · Answer 1

Perhaps some table definitions, test data, and expected output?

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 2

I can query the one box table but when I add the join and include bringing in the employees name it give me the error, and I cannot figure out which line it is. Plus I dont understand what your asking for with table definitions, sorry. But I am just trying to get the persons name, next to their EMPID and the sorted date time. (SORTDTTM.)

Lowell SSC Guru Points: 323504 More actions · Answer 3

i would guess it's due to the joins:

bd.SORTUSER_EMPID = ed.[Employee ID]

i think maybe one column is an integer or numeric column, and the other is varchar?

due to data type precedence, the varchars are being converted to integer, and some values are not actually numeric:

try converting to a varchar join for teh data type, since it's being implicitly converted anyway?

CONVERT(varchar,bd.SORTUSER_EMPID) = CONVERT(varchar,ed.[Employee ID])

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

My guess is that one of the columns bd.SORTUSER_EMPID or ed.[Employee ID] is numeric and the otherone is a varchar and contains a non-numeric value. This error would be caused by an implicit conversion. To avoid it, cast the numeric column as varchar and consider changing the data types from your columns to the adequate data types.

EDIT: Lowell had the same guess than me and posted faster than me 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 5

OldSageMonkey

SSC Veteran

Points: 265

July 22, 2013 at 12:18 pm

#1634620

Thanks Lowell, That was it. 😀