CODEWITHSUNDEEP
sqlsql-server
fizmhd
fizmhd

Reputation: 536

truncate decimal places in SQL server query not returns exact value

Select round(convert(float, 7.10), 2, 1) As Amount

Result
--------
 7.09

Why the result is coming as 7.09?

I want the result should be 7.10

Upvotes: 2

Views: 298

Answers (3)

Thom A
Thom A

Reputation: 95830

This is more a comment, however, it's easier to convey as an answer:

I think we really need more scope here. The literal value 7.10 would be interpreted (by SQL Server) as a decimal(3,2), which (on it's own) means that a simple SELECT 7.10; would return the value the OP is after.

This suggests that they actually have a column with the datatype float. If the OP just needs this for a single statement, then just converting their column to a decimal(3,2) (may need larger scale) would be sufficient:

CREATE TABLE #sample (floatcolumn float);
INSERT INTO #sample (floatcolumn)
VALUES (7.10),(9.767);

SELECT CONVERT(decimal(3,2),floatcolumn) AS decimalcolumn, floatcolumn
FROM #sample;

DROP TABLE #sample;

This doesn't, however, inherently fix the OP's datatype choice in their table. Ideally, then, they should be creating a new column and then using that going forward. Maybe something like:

USE Sandbox;
GO

CREATE TABLE SampleTable (yourcolumn float);
INSERT INTO SampleTable (yourcolumn)
VALUES (7.10),(9.767);

ALTER TABLE SampleTable ADD yourcolumn2 decimal(10,3);

UPDATE SampleTable
SET yourcolumn2 = yourcolumn;

ALTER TABLE SampleTable DROP COLUMN yourcolumn;

EXEC sp_rename 'dbo.SampleTable.yourcolumn2','yourcolumn','COLUMN';

SELECT *
FROM SampleTable;

DROP TABLE SampleTable;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You probably just want to convert to a decimal:

Select convert(decimal(4, 1), 7.10) As Amount

A decimal stores a number exactly -- up to the given number of decimal places. In addition, it changes the type of the result, to contain this information. round() merely changes the value but not the type.

Upvotes: 1

M84
M84

Reputation: 745

Try using numeric type:

Select round(convert(numeric(10,2), 7.10), 2, 1) As Amount

here you have a test!

Hope this help.

Upvotes: 2

Related Questions