asked
Round a number to 2 decimal places
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
is that a typo? you want 3026.3590 to be 3026.60? Should that read 3026.36?
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
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
Cast ( Sum...)As Decimal(10,2)
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.
If you need 10 digits before the decimal point, you can always increase the precision by 2: cast(3026.3590 as decimal(12,2))
