Solved

How to round a decimal

Posted on 2011-10-13
Medium Priority
459 Views
I have the Price field like "102.8585".

I need to round it, or round it up, like "102.8500". I need to have the last 2 digits zeros.
How can I do this? thanks

UPDATE Nop_ProductVariant
SET Price =

0
Question by:MikeMCSD
• 4
• 3
• 2

LVL 25

Expert Comment

ID: 36965990
0

LVL 25

Accepted Solution

jogos earned 1000 total points
ID: 36965999
It's not a round you want but a trunc.

that's round(102.8585 , 2 ,1)

0

LVL 39

Expert Comment

ID: 36966085
SQL Server won't store insignificant 0's for numeric data types.  So no trailing 0's after the decimal point or leading 0's before.
0

LVL 16

Author Comment

ID: 36966100
so how do I do it :

UPDATE Nop_ProductVariant
SET Price =   ????????????
0

LVL 39

Assisted Solution

BrandonGalderisi earned 1000 total points
ID: 36966147
UPDATE Nop_ProductVariant
set price =round (102.8500 , 2)
0

LVL 25

Expert Comment

ID: 36966158
UPDATE Nop_ProductVariant
SET Price = round(102.8585 , 2 ,1)

And if you are not sure it's always a good thing to try it first on a SELECT before you use something in an insert, update or delete.

SELECT  round(102.8585 , 2 )
SELECT  round(102.8516 , 2 )
SELECT  round(102.8550 , 2 )
SELECT  round(102.8585 , 2 ,1)
SELECT  round(102.8516 , 2 ,1)
SELECT  round(102.8550 , 2 ,1)
0

LVL 16

Author Comment

ID: 36966191
thanks.  . .

I need to do this for all 2000 rows.
Is this right :

UPDATE Nop_ProductVariant
SET Price = round(Price , 2 ,1)

0

LVL 25

Expert Comment

ID: 36966233
yep, if you realy want a truncate and not a round
0

LVL 16

Author Closing Comment

ID: 36966281
thanks guys
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month8 days, 1 hour left to enroll