question

rawilken avatar image
rawilken 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
t-sqlround
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
is that a typo? you want 3026.3590 to be 3026.60? Should that read 3026.36?
0 Likes 0 ·
rawilken avatar image rawilken commented ·
Correct. My bad.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rawilken avatar image rawilken commented ·
The value 3026.3590 comes from the select statement. I cannot declare it and set it!
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
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
0 Likes 0 ·
rawilken avatar image
rawilken answered
Cast ( Sum...)As Decimal(10,2)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
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.
1 Like 1 ·
Valentino Vranken avatar image Valentino Vranken commented ·
If you need 10 digits before the decimal point, you can always increase the precision by 2: cast(3026.3590 as decimal(12,2))
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.