Sql round function not working as expected

Question

0.00/5 (No votes)

See more:

, +

Hello All,

I am working on converting MS Access macros to sql,
While i converting i faced one serious issue.

Though ms access and sql are Microsoft product conversion of number works differently.
Ex.
IN MS ACCESS
546.5 =546
546.6=547
546.4=546

Whether in SQL
546.5 =547
546.6=547
546.4=546

I want to round function like how it works in MS Access
546.5 =546
546.6=547
546.4=546

What I have tried:

select cast(round(2764.6,0) as int)

Posted 8-Feb-18 20:43pm

sunil mali

Updated 8-Feb-18 22:06pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Accepted Answer · 2018-02-08T21:00:00

Solution 1

If your numbers only have one decimal position (that is there are no number like 546.51 or 546.49) you can use:

SQL

select cast(round(x-0.01,0) as int

Posted 8-Feb-18 21:00pm

Member 7870345

Comments

sunil mali 9-Feb-18 4:03am

Exact output as per my expectation :) 5/5
Thank you so much..

Maciej Los · Accepted Answer · 2018-02-08T22:06:00

Take a look at MS Access documentation:

Quote:

The Microsoft Access Round function returns a number rounded to a specified number of decimal places. However, the Round function behaves a little peculiar and uses something commonly referred to as bankers rounding. So before using this function, please read the following:

The Round function utilizes round-to-even logic. If the expression that you are rounding ends with a 5, the Round function will round the expression so that the last digit is an even number.

Conclusion: you have to create custom function which will behave as you expected

Source:
MS Access: Round Function[^]
ROUND (Transact-SQL) | Microsoft Docs[^]