Getting error during casting - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Getting error during casting

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-01-11 : 18:03:05

Hi,
I am trying to do a Sum of numbers that are stored in a varchar column. But hit an error.

Table is something like

ID (VARCHAR 12).............AMOUNT (VARCHAR 22)
001234567.....................123.12
001234567.....................10.7
023456777.....................12345.5

My query is:

SELECT
ID,
SUM (CAST(AMOUNT AS DECIMAL))
FROM table1
GROUP BY ID

But I am hitting an error below:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I will really appreciate any advice in this regards.

Thanks

Raj

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 18:10:39

You have data in one or more rows that cannot be converted to numeric. You should be able to find the rows you can't convert with:

where isnumeric(AMOUNT) <> 1

Avoid storing numeric data in varchar columns. You have just run into one of the many reasons why it is a bad idea.

CODO ERGO SUM

rkumar28
Starting Member

49 Posts

Posted - 2006-01-11 : 18:16:37

Probably I will need to avoid storing the numeric data in the varchar field.
I tried the suggested fix but the no records was returned.
My query is:

SELECT
ID,
AMOUNT
FROM table1
where
isnumeric(AMOUNT)<> 1

Will appreciate any advice on how to fix this.

Thanks

quote:
Originally posted by Michael Valentine Jones

You have data in one or more rows that cannot be converted to numeric. You should be able to find the rows you can't convert with:
where isnumeric(AMOUNT) <> 1

Avoid storing numeric data in varchar columns. You have just run into one of the many reasons why it is a bad idea.

CODO ERGO SUM

Raj

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 02:43:26

See if this helps



Select sum(cast(amount as decimal(12,2))) as total from
	(
		select '001234567' as id, '123.12' as amount union all
		select '001234567','10.7' union all
		select '023456777','12345.5' union all
		select '023456777','657f4y5.52' 
	) T where isnumeric(amount)=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources