drop trailing 0's and 1 decimal point

Hi people,

What is the best way of dropping trailing 0's? I know there is a round function, but I do not want to round I want to TRUNCATE and REMOVE the 0's and leave 1 decimal point. For example:

Number 4.510000 I want to be 4.5

I have used the function command on ROUND but it still leaves the trailing 0000

more ▼

asked Feb 17, 2012 at 04:22 PM in Default

avatar image

1.1k 56 60 66

You say you don't want to round, but what do you expect the result to be if the original number is 4.570000? Still 4.5, or 4.6

Feb 21, 2012 at 11:46 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
 SELECT CAST(ROUND(4.570000,1,1) AS decimal(10,1))

NB The third parameter to ROUND is what you are looking for. This will not round up to 4.6 but rather truncate as you per your requirements.

more ▼

answered Feb 19, 2012 at 01:47 PM

avatar image

Scot Hauder
6.5k 13 16 22

+1. I beleive the OP have forgot to do the explicit CAST/CONVERT to make sure that only 1 decimal place is returned in the output. Otherwise, the default returning data type would have been the data type of the field, after rounding (even truncating) to the specified length or precision.

Feb 20, 2012 at 07:21 AM Usman Butt

Yea I had the round function correct it was the decimal precision bit i was missing! Don't see why the round function doesn't remove the extra digits seems a bit pointless!

Feb 20, 2012 at 09:01 AM jhowe
(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



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 17, 2012 at 04:22 PM

Seen: 2516 times

Last Updated: Feb 21, 2012 at 11:46 AM

Copyright 2018 Redgate Software. Privacy Policy