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.
| Author |
Topic |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2011-03-31 : 11:06:24
|
Have following:round((cast(bp.gross_quantity as decimal(10, 2)) / 42), 2) as gross_qty which converts gallons to barrels, in this case 537796 gallons --> 12804.6700001. I do not understand why decimals are 6 long when I specified 22. Also need to convert the result (12804.670000) to a char(10) field with implicit 2 decimals embedded like 1280467Keep in mind that quantity could be negative as well.I tried this:convert(char(10), round((cast(bp.gross_quantity as decimal(10, 2)) / 42), 2)) as gross_qty but it gives overflow errorThank you. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-31 : 11:33:34
|
you specified the cast on the inside...cast(round((bp.gross_quantity / 42), 2) as decimal(10, 2)) as gross_qty Corey I Has Returned!! |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-31 : 11:37:01
|
1. the decimals are a result of the the division by 42. What you convert is only on the bp.gross_quantity.tryconvert(decimal(10,2), bp.gross_quantity / 42.0) 2. it will be fine if the result is in decimal(10,2) using the query above.convert(char(10), convert(decimal(10,2), bp.gross_quantity / 42.0))
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2011-03-31 : 12:24:59
|
Using:convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')) I got it working fine, the result: 128067Thank you guys.... |
|
|
|
|
|
|
|
|
[Resolved] Convert decimal to char