How to reduce the float length

Question

Using SQL Server 2000

I want to reduce the decimal length

Query

Select 23/12 as total

Output is showing as 1.99999999999

I don't want to round the value, I want to diplay like this 1.99

Tried Query

Select LEFT(23/12, LEN(23/12) - 3) as total

The above query is working only if there is decimal value like 12.444444, but if the total is single digit means like 12 or 4 or 11...., i am getting error at run time.

How to do this.

Need Query Help

duplicate http://stackoverflow.com/questions/1248843/formatting-numbers-in-sql-server — skajfes, Apr 16 '12 at 08:06
How are you getting 23/12 to show 1.9999... in the first place? - it should show 1.91666666666667. This might suggest there's something odd, the resolution of which might make the formatting more straight forward (as you could round instead of truncate). — Jon Egerton, Apr 16 '12 at 08:07
possible duplicate of [Truncate (not round) decimal places in SQL Server](http://stackoverflow.com/questions/44046/truncate-not-round-decimal-places-in-sql-server) — Guffa, Apr 16 '12 at 08:07
`Select 23/12 as total` gives an answer of 1; `Select 23.0/12 as total` gives 1.916666; If you're really getting 1.99999999999 then there is something more serious afoot. — Rowland Shaw, Apr 16 '12 at 08:09

score 3 · Accepted Answer · answered May 11 '12 at 20:43

There is a very simple solution. You can find it in BOL. Round takes an optional 3rd argument, which is round type. The values are round or truncate.

ROUND numeric_expression , length [ ,function ] )

...

function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

So just do

Select ROUND(cast(23 as float)/12, 2, 1) as total

That gives 1.91. Note, if you were really seeing 1.999 - something is really wrong with your computer. 23/12 = 1.916666666(ad infinitum). You need to cast one of the numbers as float since sql is assuming they're integers and doing integer division otherwise. You can of course cast them both as float, but as long as one is float the other will be converted too.

score 1 · Answer 2 · answered May 11 '12 at 20:09

Not terribly elegant, but works for all cases:

Select CONVERT(float,LEFT(CONVERT(nvarchar, 23.0/12.0),CHARINDEX('.',CONVERT(nvarchar, 23.0/12.0)) + 2)) as total

Scalar Function

-- Description: Truncate instead of rounding a float
-- SELECT dbo.TruncateNumber(23.0/12.0,2)
-- =============================================
CREATE FUNCTION TruncateNumber
(
    -- Add the parameters for the function here
    @inFloat float,
    @numDecimals smallint
)
RETURNS float
AS
BEGIN
    IF (@numDecimals < 0)
    BEGIN
        SET @numDecimals = 0
    END
    -- Declare the return variable here
    RETURN CONVERT(float,LEFT(CONVERT(nvarchar, @inFloat),CHARINDEX('.',CONVERT(nvarchar, @inFloat)) + @numDecimals)) 

END
GO

Truncate is a built in option of round, no need to reinvent the wheel here. — Brian White, May 11 '12 at 20:44

How to reduce the float length

2 Answers2