Re: Error convert varchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Re: Error convert varchar to numeric

Author

Topic

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-20 : 08:21:45

Hallo,

I have the following select query (shown below) when I run this query it gives me an error stating "error converting varchar to numeric".

The problem is when the follwoing is added CONVERT(decimal(10, 2), num_pats * 0.025) AS AmounttobePaid . Also please note; num_pats has been set as varchar(50) on the design level. I dont want to change num_pats to numeric but still want to have my AmounttobePaid field added to the query any ideas... Thanks

SELECT TOP (100) PERCENT prac_no, prac_eid, num_pats, CONVERT(decimal(10, 2), num_pats * 0.025) AS AmounttobePaid
FROM gprdsql.TblPracDetails
WHERE (pay_status = 'Practice to be paid') AND (prac_status = 'Active' OR prac_status = 'On Hold')
ORDER BY prac_no

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 08:25:09

Try this:
(CONVERT(decimal(10, 2), num_pats)* 0.025) AS AmounttobePaid

Notice that I moved the multiplication outside the conversion. The error was that num_pats was being multiplied by 0.025 before being converted into a decimal.

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-20 : 08:35:41

Thanks, when I changed the code to (CONVERT(decimal(10, 2), num_pats)* 0.025) AS AmounttobePaid
It works without any error. However, it gives values of AmounttobePaid as 234.65000, 176.27500, 302.17500 etc

I only want it to give me to 2 decimal places such as 234.65, 176.26 etc any ideas.. thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:37:30

WhiteFang, that will still not work since OP has varchar for source column.
It simply has invalid data in it.

Jamilahmed, you will have to check for valid data when casting your source values.

SELECT		prac_no,
		prac_eid,
		num_pats,
		case patindex('%[^0-9]%', num_pats)
			when 0 then CONVERT(decimal(10, 2), num_pats * 0.025) 
			else null
		end AS AmounttobePaid
FROM		gprdsql.TblPracDetails
WHERE		pay_status = 'Practice to be paid'
		AND prac_status IN ('Active', 'On Hold')
ORDER BY	prac_no

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:39:33

And WhiteFang, you also broke Jamilahmed's business rule by changing the casting.
With your example, the data is not longer rounded to two decimals.

DECLARE	@i INT

SET	@i = 35

SELECT	CONVERT(decimal(10, 2), @i * 0.025) AS Original,
	CONVERT(decimal(10, 2), @i) * 0.025 AS WhiteFang

E 12°55'05.63"
N 56°04'39.26"

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-20 : 08:42:30

Thanks for your suggestion Peso, however, I am sure the data is correct. However, the only issue is to convert for example 234.65000 to 234.65. Shouldn't this be much simpler?

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-20 : 08:44:18

Peso how can incorporate the above in my code.. Thanks

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 08:50:22

CONVERT(decimal(10, 2),CONVERT(decimal(10, 2), num_pats)* 0.025)

Keep it simple.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:51:30

No problem

DECLARE	@Sample TABLE
	(
		num_pats VARCHAR(20)
	)

INSERT	@Sample
SELECT	'234.65000' UNION ALL
SELECT	'234.65'

SELECT	num_pats,
	CONVERT(decimal(10, 2), CAST(num_pats AS DECIMAL(12, 4)) * 0.025)
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:52:09

E 12°55'05.63"
N 56°04'39.26"

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-20 : 08:59:07

Thanks Peso and whitefang very helpful indeed.

Whitefang the CONVERT(decimal(10, 2),CONVERT(decimal(10, 2), num_pats)* 0.025) worked perfectly. .

Thank you all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:02:30

Beware that WhiteFang round of source data too soon, due to a flaw in his suggestion.
See this example

DECLARE	@Sample TABLE
	(
		num_pats VARCHAR(20)
	)

INSERT	@Sample
SELECT	'234.9980' UNION ALL
SELECT	'234.65'

SELECT	num_pats,
	CONVERT(decimal(10, 2), CAST(num_pats AS DECIMAL(10, 2)) * 0.025) AS WhiteFang,
	CONVERT(decimal(10, 2), CAST(num_pats AS DECIMAL(12, 4)) * 0.025) AS Peso
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 09:05:45

Yes, there is a loss of precision when using my suggestion in certain cases. Either way, you shouldn't be storing it as a non-numeric value to begin with AND this should be considered part of "business logic" at the application layer.

I'd use Peso's suggestion if this is a critical app.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources