Round a number to 2 decimal places

 0 I have a procedure that when run returns a value for Price as 3026.3590 I want this to be 3026.60 ``````CREATE PROCEDURE spCharterPrice(@CharTrip NUMERIC) AS DECLARE @FuelCharge money = 0.79 -- VARIABLE PER GALLON FUEL RATE , @WaitCharge money = 112.50 -- VARIABLE PER HOUR WAIT RATE , @Price decimal(10,2) BEGIN SELECT C.CHAR_TRIP , SUM (([MOD_CHG_MILE]*[CHAR_DISTANCE]) + ([CHAR_FUEL_GALLONS]* @FuelCharge) + ([CHAR_HOURS_WAIT]* @WaitCharge)) as Price FROM AIRCRAFT A Inner Join CHARTER C on A.AC_NUMBER = C.AC_NUMBER Inner Join MODEL M on A.MOD_CODE = M.MOD_CODE WHERE C.CHAR_TRIP = @CharTrip GROUP BY C.CHAR_TRIP, C.AC_NUMBER END `````` more ▼ asked Nov 21, 2010 at 05:18 AM in Default rawilken 101 ● 8 ● 11 ● 11 Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 is that a typo? you want 3026.3590 to be 3026.60? Should that read 3026.36? Nov 21, 2010 at 05:32 AM Kev Riley ♦♦ Correct. My bad. Nov 21, 2010 at 05:55 AM rawilken add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 Either explicitly round it using the `round` function ``````select round(3026.3590,2) ``````or implicitly round it by setting the datatype ``````declare @price decimal(10,2) set @price = 3026.3590 `````` select @price more ▼ answered Nov 21, 2010 at 05:35 AM Kev Riley ♦♦ 53.9k ● 47 ● 49 ● 76 The value 3026.3590 comes from the select statement. I cannot declare it and set it! Nov 21, 2010 at 05:55 AM rawilken Just use `round` then - I wasn't meaning you to use another variable, I was just demonstrating that if the resultant datatype is set, then there is implicit conversion Nov 21, 2010 at 06:06 AM Kev Riley ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Cast ( Sum...)As Decimal(10,2) more ▼ answered Nov 21, 2010 at 06:19 AM rawilken 101 ● 8 ● 11 ● 11 but be careful with this - I don't know the underlying data types which is why I didn't suggest it - but this will limit you to 8 digits before the decimal point - is that ok? Using `round` will return the same datatype as passed in. Nov 21, 2010 at 06:49 AM Kev Riley ♦♦ If you need 10 digits before the decimal point, you can always increase the precision by 2: cast(3026.3590 as decimal(12,2)) May 03, 2011 at 05:21 AM Valentino Vranken add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x6

asked: Nov 21, 2010 at 05:18 AM

Seen: 5240 times

Last Updated: Nov 21, 2010 at 09:08 AM