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

avatar image

rawilken
101 10 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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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

avatar image

rawilken
101 10 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.

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:

x1069
x8

asked: Nov 21, 2010 at 05:18 AM

Seen: 6226 times

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

Copyright 2016 Redgate Software. Privacy Policy