Re: Numeric rounding not working?

From: Steve Kass (skass_at_drew.edu)
Date: 10/21/04 

Date: Wed, 20 Oct 2004 20:02:02 -0400

Edward,

  The data type decimal holds zero digits after the decimal point, so
your four variables will have the values 27, -80, 27, and -80
respectively. The distance between the points is then zero, though you
see a result of a few inches because of the non-exact trig calculations.

  If you change the data type for the coordinates to float, or to a
decimal type like decimal(20,16), you'll get an answer of about 9.22
miles. I think that's the correct answer, not 6. You might also want
to use the built in RADIANS function for more precision than you get
from 57.2958.

declare @lat1 as float
declare @lon1 as float
declare @lat2 as float
declare @lon2 as float

set @lat1 = radians(26.7174)
set @lon1 = radians(-80.0695)
set @lat2 = radians(26.5956)
set @lon2 = radians(-80.1302)

declare @EquationResult as Float
set @EquationResult = 3963 * acos(sin(@lat1) *
sin(@lat2) + cos(@lat1) * cos(@lat2) *
cos(@lon2 - @lon1))
select @EquationResult as [Original Result]

Steve Kass
Drew University

adude wrote:

>I am trying to get the result of an equation (which has many decimal
>places) to round to the nearest whole number. But everytime I try
>casting it to round I get a zero.
>
>Here is the sql script:
>-----------------------
>declare @lat1 as decimal
>declare @lon1 as decimal
>declare @lat2 as decimal
>declare @lon2 as decimal
>
>set @lat1 = 26.7174
>set @lon1 = -80.0695
>set @lat2 = 26.5956
>set @lon2 = -80.1302
>
>declare @EquationResult as Float
>set @EquationResult = 3963 * acos(sin(@lat1/57.2958) *
>sin(@lat2/57.2958) + cos(@lat1/57.2958) * cos(@lat2/57.2958) *
>cos(@lon2/57.2958 - @lon1/57.2958))
>select @EquationResult as [Original Result]
>
>select cast(@EquationResult as decimal(1)) as [Error Result]
>select cast(5.6053301811218262 as decimal(1)) as [Almost Correct]
>
>RESULTS
>========
>Original Result: 5.9053301811218262E-5
>Error Result: 0
>Almost Correct: 6
>
>-------------------
>If I try casting the @EquationResult variable that has the "Original
>Result" value of 5.9053301811218262E-5 then I get "Error Result" 0 which
>is not the rounding I was expecting to happen.
>
>I noticed that if I remove the E-5 from the "Original Result" then I try
>casting it again then I get the "Almost Correct" value that I am looking
>for. I say "Almost Correct" because I have not been able to figure out a
>way to take the "Original Result" value (@EquationResult) and round from
>there.
>
>Help!
>
>Sincerely,
>
>Edward Burns
>
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!
>
>

Relevant Pages

  • Re: sound synthesis
    ... > the declarations and using short float helped, ... (defun mix (target-samples source-samples start sample-rate) ... (declare (float sample-rate seconds) ... (defun fm-gong (time freq) ...
    (comp.lang.lisp)
  • Re: Extending T-SQL with COM
    ... Using Excel for this is an extremely heavy weight way of performing what ... declare @rate float ... > GRANT EXECUTE ON dbo.sp_hexadecimal TO Public ...
    (microsoft.public.sqlserver.programming)
  • Re: newbie help.
    ... declare them on one line each for clarity. ... 'double' data type, rather than the 'float' type. ... | cin>>salary; ...
    (alt.comp.lang.learn.c-cpp)
  • Re: fgets problem
    ... float f; ... Your array "f" is used only inside main, so there's no reason to ... declare it at file scope. ... can invoke undefined behavior in some cases. ...
    (comp.lang.c)
  • Re: Floating point arithmetic support in DCL
    ... If A had been previously declared as float, ... > the data type of the value that is assigned to them. ... > assignment it should contain the value 1 and be of type integer. ... Consider this - introduce a new assignment mechanism to declare ...
    (comp.os.vms)