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 Wednesday, July 15, 2009 12:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 8:42 AM
Points: 4, Visits: 106
Hello,

I have a problem in one of my production server database.

when i execute following query in production database
select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83)
and Convert(decimal(15,0),Serial_No) = 128249

it is giving following error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

The same query executing successfully in Dev server. Both table properties are same on Prod and Dev.

Steps Tried:

When i add "trim" string function to the query
"select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83) and Convert(decimal(15,0),ltrim(Serial_No)) = 128249"

query executing successfully and fetching records.

Exported table to the same server on another database query executing successfully and fetching records without "trim" string function.


Both SQL versions are same on Production and Development servers.
Both are 8.00.2282

Serial_No is Varchar(20) data type,

I am trying to convert varchar into decimal data type.


Appricate your help



Kindest Regards,

klss

Post #753182
Posted Wednesday, July 15, 2009 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
It depends on the data stored in the table. Try running this:

SELECT Original_Dollar_Amt , Serial_No FROM Properties where ISNUMERIC(Original_Dollar_Amt) = 0 OR ISNUMERIC(Serial_No) = 0

Does it return rows? If so, fix the values and there you are.

Hope this helps
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #753353
Posted Wednesday, December 22, 2010 11:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 10:14 AM
Points: 27, Visits: 30
clean and crisp solution

http://www.a2zmenu.com/MySql/Arithmetic-overflow-error.aspx
Post #1038415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse