Round Money Field to 2 decimals

Posted on 2006-03-27
Last Modified: 2008-01-09
How do you round a money field to 2 decimals?

I would have thought rount(field, 2) would work but it doesn't

I only want to return:

Original Value = 12.040506  -----   Return Value =  12.04

Question by:DJMoonLight
    LVL 50

    Expert Comment

    by:Steve Bink
    What does yours return?  This should be the proper syntax:

    round(myfield, 2)

    ROUND ( numeric_expression, length [ , function ] )

    An expression of the exact numeric or approximate numeric data type categories, or types that are implicitly convertible to float.

    The precision to which numeric_expression is to be rounded. When length is a positive number, numeric_expression is rounded to the number of decimal places specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

    The type of operation to perform. 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.


    Author Comment

    Mine returns the number rounded to that many in the decimal..
    For example:

    17.701010 = 17.700000

    I only want 17.70 returned

    LVL 15

    Accepted Solution

    I think the round is working.  maybe you are assigning the result to a float or other numeric datatype.  You can do this instead of using round.

    Declare @RoundValue decimal(18,2)

    select @RoundValue = CAST([Original Value] as decimal(18,2))

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    CAST(round(myfield, 2) as Numeric(18,2))

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now