Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error converting data type varchar to numeric Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 12:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 8:23 AM
Points: 11, Visits: 17
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())

Post #1476179
Posted Monday, July 22, 2013 12:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 1,269, Visits: 3,635
Perhaps some table definitions, test data, and expected output?

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1476186
Posted Monday, July 22, 2013 12:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 8:23 AM
Points: 11, Visits: 17
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.)
Post #1476188
Posted Monday, July 22, 2013 12:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,952, Visits: 32,476
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1476189
Posted Monday, July 22, 2013 12:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 4,041, Visits: 9,189
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476190
Posted Monday, July 22, 2013 12:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 8:23 AM
Points: 11, Visits: 17
Thanks Lowell, That was it.
Post #1476193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse