Remove trailing zeros from decimal in SQL

Question

5.00/5 (3 votes)

See more:

is Fun can Do this :

VB

20 -> 20

20.00 -> 20

20.5 -> 20.5

20.5000 -> 20.5

20.125 -> 20.125

20.12500 -> 20.125

0.000 -> 0

in sql??

Posted 8-Jan-14 22:52pm

S.Dwaik

Updated 6-Oct-22 19:52pm

Add a Solution

Comments

phil.o 9-Jan-14 4:55am

Not clear : the database engine will return the values as raw decimals/reals/etc...
What you want to do concerns the presentation of the data, thus should not be managed from the database system, but rather from the presentation layer.

4 solutions

Solution 3

Realistically, this is a job for your presentation layer. The values from SQL should be correct. Your presentation layer should care about how they are displayed.

Posted 9-Jan-14 10:56am

Christian Graus

Solution 4

SELECT CAST (SUM(Amount) as decimal(10,2))

Posted 6-Oct-22 19:52pm

Member 15790063

Comments

CHill60 7-Oct-22 5:19am

Reasons for my downvote: if you take some of the specific examples the OP used your attempt returns the wrong values. E.g. Instead of 20.12500 -> 20.125 your version gives 20.12500 -> 20.13.
Instead of 20.5000 -> 20.5 yours gives 20.5000 -> 20.50

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)

Aarti Meswania · Accepted Answer · 2014-01-08T22:56:00

Solution 1

try this,

SQL

SELECT convert(DOUBLE PRECISION, 20.00)
SELECT convert(DOUBLE PRECISION, 20.50)
SELECT convert(DOUBLE PRECISION, 20.125)
SELECT convert(DOUBLE PRECISION, 20.12500)

Happy Coding!
:)

Posted 8-Jan-14 22:56pm

Aarti Meswania

Comments

Maciej Los 9-Jan-14 4:57am

Welcome back, Aarti ;) Happy New Year!
+5!

Aarti Meswania 9-Jan-14 4:59am

hi, Maciej Los
Happy new year!
thanks for upvoting :)

Animesh Datta · Accepted Answer · 2014-01-08T23:53:00

Try this also,

SQL

select cast(CAST(12.5800 as decimal(18,5)) as float)
select cast(CAST(12.5 as decimal(18,5)) as float)
select cast(CAST(12.58 as decimal(18,5)) as float)
select cast(CAST(0.00 as decimal(18,5)) as float)

and the out put will be

SQL

respectively

thanks
animesh

Remove trailing zeros from decimal in SQL

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us