basic sql calculation - 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

General SQL Server Forums

New to SQL Server Programming

basic sql calculation

Author

Topic

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-06-22 : 15:13:35

hi,

i want to calculate this but getting conversion erro

Declare @TempproductPrice as varchar(100)
Declare @productPrice as varchar(10)
@productPrice= '1000'

set @TempproductPrice =((convert(int, ISNULL(@productPrice,0)) *10)/100)

Error is :

Conversion failed when converting the varchar value '37.00' to data type int.

plz suggest.thx

http://webdevlopementhelp.blogspot.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-22 : 15:31:26

I'm not going to address the horrible data tpyes (why is price a varchar 100?!!),
but try this

set @TempproductPrice =((convert(numeric(10,0), ISNULL(@productPrice,0)) *10)/100)

The better way would be to change the underlying data types.

Jim

Everyday I learn something that somebody else already knew

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-06-23 : 07:29:08

quote:
Originally posted by jimf

I'm not going to address the horrible data tpyes (why is price a varchar 100?!!),
but try this

set @TempproductPrice =((convert(numeric(10,0), ISNULL(@productPrice,0)) *10)/100)

The better way would be to change the underlying data types.

Jim

Everyday I learn something that somebody else already knew

hi thx it works , i have one more quesiton on it
if my query return 6.6 then i want result as 7 instead of 6
and if its 6.4 then 6

wht i can do in this case

thanx

http://webdevlopementhelp.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-23 : 13:38:25

quote:
Originally posted by kirank

quote:
Originally posted by jimf

I'm not going to address the horrible data tpyes (why is price a varchar 100?!!),
but try this

set @TempproductPrice =((convert(numeric(10,0), ISNULL(@productPrice,0)) *10)/100)

The better way would be to change the underlying data types.

Jim

Everyday I learn something that somebody else already knew

hi thx it works , i have one more quesiton on it
if my query return 6.6 then i want result as 7 instead of 6
and if its 6.4 then 6

wht i can do in this case

thanx

http://webdevlopementhelp.blogspot.com

use

round(column,0)

for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources