Solved

ROUND in SQL query

Posted on 1998-08-31
5
1,103 Views
Last Modified: 2012-06-27
In a SQL query, I use the ROUND function  to round a double type field with n decimals. The problem is that it rounds to the closiest even number, and I'd like to round to  the closiest integer !
For example : ROUND(1.5 , 0) ==> 2
                      ROUND(2.5 , 0) ==> 2

I want : MyFunction(1.5, 0) ==> 2
             MyFunction(2.5, 0) ==> 3

Same thing with n decimals
(I have a solution in ACCESS but not in SQL-SERVER and ORACLE: under ACCESS it is like that :
...SET amount =  fix(amount*rate*10^NbDecimals+0.5)/10^NbDecimals

Thank you for help.
0
Comment
Question by:gosselin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 2

Expert Comment

by:threshold
ID: 1089858
In my SQL Server 6.5, i got

ROUND(2.5 , 0) ==> 3
0
 

Author Comment

by:gosselin
ID: 1089859
Your are rigth. This result is obtain with the CLng function of ACCESS, I was completly wrong. So there is no question any more.

Thank you very much
How can I give you the points ?

0
 
LVL 2

Expert Comment

by:threshold
ID: 1089860
You r welcomed.
Keep it...
Delete the question...
0
 
LVL 3

Expert Comment

by:altena
ID: 1089861
You really deserve the points treshold.

Its the little things like this that can really
eat one's time away. And with hindsight it is
oh so simple :-)

---Cheers
0
 
LVL 2

Accepted Solution

by:
threshold earned 300 total points
ID: 1089862
Ok, Thanks.... I repost my 'note' again.

In my SQL Server 6.5, i got
ROUND(2.5 , 0) ==> 3
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question