formating the number

Question

Post reply

formating the number

Ponnuru venkateswarlu

SSC-Addicted

Points: 418
More actions
August 24, 2004 at 6:47 am

#181499

hi,
I have some calculation and i want to display like this.
34.2345 ---> I want to display as 34.23 (I don't want to round the value). Can anyone help on this issue.
In simple, i want equivalent function or oracle format in sql server.
rgds,
venkat

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Try this:

--- DECLARE VARIABLES

DECLARE @MYVAL DECIMAL (8,4) ---ORIGINAL VALUE

DECLARE @MYVAL_CONVERTED NVARCHAR(50) ---CONVERT VAL TO STRING

DECLARE @STARTPOINT AS INTEGER ---POSITION OF DECIMAL POINT

--- SET VARIABLES

SET @MYVAL = 34.2345

SET @MYVAL_CONVERTED = CAST(@MYVAL AS NVARCHAR(50))

SELECT @STARTPOINT = PATINDEX('%.%',@MYVAL_CONVERTED)

--- LOGIC

SELECT SUBSTRING (@MYVAL_CONVERTED, 0,@STARTPOINT+1) +

SUBSTRING (@MYVAL_CONVERTED, @STARTPOINT+1,2)

------------------------------
The Users are always right - when I'm not wrong!

Ponnuru venkateswarlu SSC-Addicted Points: 418 More actions · Answer 2

hi,

I want to do that in SQL not by writing Stored Procedure.

rgds,

venkat

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 3

Your example is not very good, because the result is the same if you want to round the number to 2 decimals or truncate it to 2 decimals. So let's create a table with some better test data:

CREATE TABLE TestData (number numeric(10,4))

INSERT INTO TestData VALUES (34.2345)
INSERT INTO TestData VALUES (34.2377)
INSERT INTO TestData VALUES (-34.2345)
INSERT INTO TestData VALUES (-34.2377)

SELECT number, ROUND(number,2) as a, STR(number,10,2) as b, 
ROUND(number-0.005,2) as c, 
ROUND(ABS(number)-0.005,2)*SIGN(number) as d
FROM TestData

DROP TABLE TestData

Choose the column you like best...

Razvan

Ankush Arakere Choudera SSC Enthusiast Points: 154 More actions · Answer 4

USE "round(34.2365,2,1)", which will truncate to 34.23.

- Ankush

cedric-166142 SSC Rookie Points: 38 More actions · Answer 5

"select round(34.2365,2,1) " doesn't solve your problem as it displays "34.2300"

Razvan's proposed solution is best ,but should you wish to do calculations on it you will have to convert back to numeric .

I'm just taking a part of his solution which I think best suits your requirement

eg.

-- QUERY

1.

SELECT CONVERT(numeric(10,2),STR(34.2345,10,2))

2. Testing negatives

SELECT CONVERT(numeric(10,2),STR(-34.2345,10,2))

-- RESULT

-- So now the result is stil in a numeric format which you can

-- do calculations on ,should the need arise

1.34.23

2.-34.23

Cedric

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 6

Cedric and Razvan - I am afraid your examples do not work. They still round up the value when I try them in QA. Try your examples out with 34.2399 and you will see. They all come to 34.24.

Looks like Ankush has the answer with a convert back to numeric in front:

select CONVERT(numeric(10,2),round(34.2365,2,1))

------------------------------
The Users are always right - when I'm not wrong!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Why mess around with rounding when rounding is to be avoided?

Just cast the decimal to a string and chop it from left side to 2nd position after the decimal point

(assuming all cases are like the example)

declare @d decimal(6,4)

set @d = 34.2399

select @d

select substring(cast(@d as varchar(11)), 1, charindex('.', cast(@d as varchar(11))) + 2)

--------

34.2399

(1 row(s) affected)

-----------

34.23

(1 row(s) affected)

/Kenneth

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 8

Believe it or not, the query:

CONVERT(numeric(10,2),round(34.2365,2,1))

... is the most efficient out of all the examples. Looking at statistics it takes 0ms to run, whereas all the others take at least 1ms. All on the basis of explicitly supplying the number inside the code rather than through a variable.

------------------------------
The Users are always right - when I'm not wrong!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

case when x < 0 then ceiling( x * 100 ) / 100

else floor( x * 100 ) / 100 end