Round a number to 2 decimal places

Question

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

Answer 1

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

answered Nov 21 '10 at 05:35 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

The value 3026.3590 comes from the select statement. I cannot declare it and set it!

Nov 21 '10 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 '10 at 06:06 AM Kev Riley ♦♦

(comments are locked)

Answer 2

0

Cast ( Sum...)As Decimal(10,2)

answered Nov 21 '10 at 06:19 AM

rawilken
101 ● 5 ● 8 ● 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 '10 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 '11 at 05:21 AM Valentino Vranken

(comments are locked)

Round a number to 2 decimal places

Follow this question

Related Questions