x

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
more ▼

asked Nov 21, 2010 at 05:18 AM in Default

rawilken gravatar image

rawilken
101 8 11 11

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
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 gravatar image

Kev Riley ♦♦
52.7k 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 ♦♦
(comments are locked)
10|1200 characters needed characters left
Cast ( Sum...)As Decimal(10,2)
more ▼

answered Nov 21, 2010 at 06:19 AM

rawilken gravatar image

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
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x985
x6

asked: Nov 21, 2010 at 05:18 AM

Seen: 4996 times

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