SQL round to the nearest hundredth

Question

UPDATE prodfeatures2 SET featureprice = featureprice * 0.6316;

I am trying to setup a round of to the nearest hundredth. HELP!

I do not want the answer to be 104.7648, I would want it to be 104.76.

I do not want the answer to be 104.7668, I would want it to be 104.77.

score 5 · Accepted Answer · answered Oct 28 '10 at 02:43

5

UPDATE prodfeatures2 SET featureprice = ROUND(featureprice * 0.6316,2)

answered Oct 28 '10 at 02:43

spender

117,338
33
229
351

3

Please note that Round is for statistical purposes and may not suit financial rounding. it is likely that accounts would like ROUND(104.745,2) to equal 104.75, not 104.74, which is what will be returned. – Fionnuala Oct 28 '10 at 12:33
1

...that is, VBA Round() uses "banker's rounding." – David-W-Fenton Oct 28 '10 at 23:03

score 2 · Answer 2 · answered Oct 28 '10 at 08:06

2

I don't think you have provided enough data to deduce which rounding algorithm you require.

If your spec tells you which rounding algorithm to use then please post it.

If your spec fails to tell you which rounding algorithm then raise the issue with the designer.

Generally speaking, SQL isn't designed for math calculations. Consider doing rounding in another tier. If you do, be sure to store values using DECIMAL with an additional decimal place than required in the front end.

answered Oct 28 '10 at 08:06

onedaywhen

55,269
12
100
138

While I agree that in a lot of cases rounding is a presentation layer issue, if you're calculating a total of an invoice, for example, you probably want to round the total of each invoice item before totalling the invoice. If you don't, your printout may not match what is returned by SQL-based calculations. – David-W-Fenton Oct 28 '10 at 23:05

score 2 · Answer 3 · edited May 23 '17 at 11:53

2

Please see: How to Round in MS Access, VBA

To quote an excerpt:

"The Round function performs round to even, which is different from round to larger." --Microsoft

Format always rounds up.

  Debug.Print Round(19.955, 2)
  'Answer: 19.95

  Debug.Print Format(19.955, "#.00")
  'Answer: 19.96

In this case:

UPDATE prodfeatures2 SET featureprice = CCUR(Format(featureprice * 0.6316,'#.00'))

edited May 23 '17 at 11:53

Community

1
1

answered Oct 28 '10 at 12:23

Fionnuala

90,370
7
114
152

"Format always rounds up" -- oh no it doesn't e.g. `SELECT Format(-19.955, "#.00")` rounds down to `-19.96`. – onedaywhen Oct 29 '10 at 08:41
It rounds up on the absolute value, which with negative numbers in, say, credit memos, is what you want. – David-W-Fenton Oct 29 '10 at 23:34

chembu venu · Answer 4 · 2012-12-19T13:09:23.277

1

SELECT ROUND(cast(104.7668 as decimal(6,2)),2) as roundof;

edited Dec 19 '12 at 13:09

answered Dec 19 '12 at 12:56

chembu venu

19
2

1

It would be more helpful to explain your answer, link to some sources, and format your code. – JSuar Dec 19 '12 at 13:15

score 1 · Answer 5 · answered Oct 28 '10 at 02:42

1

ROUND(TheValue, 2)

answered Oct 28 '10 at 02:42

dan04

87,747
23
163
198

Please apply that to this update statement, so that I can see where to place that: UPDATE prodfeatures2 SET featureprice = featureprice * 0.6316; – Will Fix Oct 28 '10 at 02:45

onedaywhen · Answer 6 · 2010-11-02T06:08:35.743

Here's my MS-Access specific answer: there's something fishy about the question!

Unless featureprice is an extremely large or extremely large small amount, and the metadata suggests that it is not, multiplying by a decimal literal such as 0.6316 will coerse the result to type DECIMAL.Now, by nature, the DECIMAL type in Access (ACE, Jet, whatever), rounds by truncation e.g. if you could do this:

SELECT CAST(104.7668 AS DECIMAL(17, 2)

it would round to 104.76... of course you can't do this in Access because it doesn't support the SQL Standard syntax and its own proprietary syntax CDEC() was broken from day one and still hasn't been fixed in ACE (rolls eyes). But what you can do is this:

CREATE TABLE TestDecimal 
(
 dec_col DECIMAL(17, 2) NOT NULL UNIQUE
);

INSERT INTO TestDecimal (dec_col) 
   VALUES (104.7668);

SELECT dec_col FROM TestDecimal;
-- 104.76 -- truncated

I'm going to guess that your prodfeatures2 column is type CURRENCY and I suggest that if don't want your result to be cast as a DECIMAL, and what we can tell from your algorithm you do not, then your SQL is missing a cast.

Further, you want the result to be two decimal places, yet the original values are not to two decimal places. For example:

SELECT CCUR(CCUR(165.87) * 0.6316)
-- 104.7635 -- too low

SELECT CCUR(CCUR(165.88) * 0.6316)
-- 104.7698 -- too high

SELECT CCUR(CCUR(165.872) * 0.6316)
-- 104.7648 -- spot on

So the values are failing to be rounded to two dp by an earlier process but needs to be two dp after this process? As I say, something may smell here and you have bugs you haven't yet tracked down... or there's more to this than you are revealing here.

What is the basis for your assertion that multipying by a decimal coerces the result to a decimal data type?

(Tongue in cheek) Why, I read it in the user manual for ACE/Jet of course. Only joking, there isn't one. Like anything in Jet 4.0, you just have experiment.

Decimal literals (with exceptions e.g. extremely large and extremely small values) are of type DECIMAL. For example:

SELECT TYPENAME(0.1)

returns 'Decimal'.

When using the numeric operators (add, subtract, multiply and divide) involving a value of type DECIMAL will coerce the result to type DECIMAL (the same exceptions as above apply).

A simple yet effective test is to create a table with one column for each of the numeric data types, insert a small value (say 1) for each, then add/subtract/multiply/divide all by a decimal literal (say 0.1):

SQL DDL:

CREATE TABLE TestNumericDataTypes
(
 TINYINT_col TINYINT NOT NULL, 
 SMALLINT_col SMALLINT NOT NULL, 
 INTEGER_col INTEGER NOT NULL, 
 REAL_col REAL NOT NULL, 
 FLOAT_col FLOAT NOT NULL, 
 DECIMAL_col DECIMAL NOT NULL, 
 CURRENCY_col CURRENCY NOT NULL, 
 YESNO_col YESNO NOT NULL, 
 DATETIME_col DATETIME  NOT NULL
);

SQL DML:

INSERT INTO TestNumericDataTypes 
(
 TINYINT_col, SMALLINT_col, INTEGER_col, 
 REAL_col, FLOAT_col, DECIMAL_col, 
 CURRENCY_col, YESNO_col, DATETIME_col
) 
VALUES (1, 1, 1, 1, 1, 1, 1, 1, 1);

SQL DML:

SELECT TYPENAME(TINYINT_col * 0.1), 
       TYPENAME(SMALLINT_col * 0.1), 
       TYPENAME(INTEGER_col * 0.1), 
       TYPENAME(REAL_col * 0.1), 
       TYPENAME(FLOAT_col * 0.1), 
       TYPENAME(DECIMAL_col * 0.1), 
       TYPENAME(CURRENCY_col * 0.1), 
       TYPENAME(YESNO_col * 0.1), 
       TYPENAME(DATETIME_col * 0.1),
       TYPENAME(TINYINT_col / 0.1), 
       TYPENAME(SMALLINT_col / 0.1), 
       TYPENAME(INTEGER_col / 0.1), 
       TYPENAME(REAL_col / 0.1), 
       TYPENAME(FLOAT_col / 0.1), 
       TYPENAME(DECIMAL_col / 0.1), 
       TYPENAME(CURRENCY_col / 0.1), 
       TYPENAME(YESNO_col / 0.1), 
       TYPENAME(DATETIME_col / 0.1),
       TYPENAME(TINYINT_col + 0.1), 
       TYPENAME(SMALLINT_col + 0.1), 
       TYPENAME(INTEGER_col + 0.1), 
       TYPENAME(REAL_col + 0.1), 
       TYPENAME(FLOAT_col + 0.1), 
       TYPENAME(DECIMAL_col + 0.1), 
       TYPENAME(CURRENCY_col + 0.1), 
       TYPENAME(YESNO_col + 0.1), 
       TYPENAME(DATETIME_col + 0.1),
       TYPENAME(TINYINT_col - 0.1), 
       TYPENAME(SMALLINT_col - 0.1), 
       TYPENAME(INTEGER_col - 0.1), 
       TYPENAME(REAL_col - 0.1), 
       TYPENAME(FLOAT_col - 0.1), 
       TYPENAME(DECIMAL_col - 0.1), 
       TYPENAME(CURRENCY_col - 0.1), 
       TYPENAME(YESNO_col - 0.1), 
       TYPENAME(DATETIME_col - 0.1)
FROM TestNumericDataTypes;

I'm not sure whether you can create all these types via the Access interface nad you may not know how to run SQL DDL so here's some vanilla VBA (Access not required e.g. can be run from Excel, no references required e.g. just copy and paste):

Sub TestAccessDecimals()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String
      Sql = _
          "CREATE TABLE TestNumericDataTypes" & vbCr & "(" & vbCr & " TINYINT_col TINYINT NOT NULL, " & vbCr & " SMALLINT_col SMALLINT NOT NULL, " & vbCr & " INTEGER_col INTEGER NOT NULL, " & vbCr & " REAL_col REAL NOT NULL, " & vbCr & " FLOAT_col FLOAT NOT" & _
          " NULL, " & vbCr & " DECIMAL_col DECIMAL NOT NULL, " & vbCr & " CURRENCY_col CURRENCY NOT NULL, " & vbCr & " YESNO_col YESNO NOT NULL, " & vbCr & " DATETIME_col DATETIME  NOT NULL" & vbCr & ");"
      .Execute Sql

      Sql = _
          "INSERT INTO TestNumericDataTypes " & vbCr & "(" & vbCr & " TINYINT_col, SMALLINT_col, INTEGER_col, " & vbCr & " REAL_col, FLOAT_col, DECIMAL_col, " & vbCr & " CURRENCY_col, YESNO_col, DATETIME_col" & vbCr & ") " & vbCr & "VALUES (1, 1, 1, 1, 1, 1," & _
          " 1, 1, 1);"
      .Execute Sql

      Sql = _
          "SELECT TYPENAME(TINYINT_col * 0.1), " & vbCr & "       TYPENAME(SMALLINT_col * 0.1), " & vbCr & "       TYPENAME(INTEGER_col * 0.1), " & vbCr & "       TYPENAME(REAL_col * 0.1), " & vbCr & "       TYPENAME(FLOAT_col * 0.1)," & _
          " " & vbCr & "       TYPENAME(DECIMAL_col * 0.1), " & vbCr & "       TYPENAME(CURRENCY_col * 0.1), " & vbCr & "       TYPENAME(YESNO_col * 0.1), " & vbCr & "       TYPENAME(DATETIME_col * 0.1)," & vbCr & "       TYPENAME(TINYINT_col / 0.1)," & _
          " " & vbCr & "       TYPENAME(SMALLINT_col / 0.1), " & vbCr & "       TYPENAME(INTEGER_col / 0.1), " & vbCr & "       TYPENAME(REAL_col / 0.1), " & vbCr & "       TYPENAME(FLOAT_col / 0.1), " & vbCr & "       TYPENAME(DECIMAL_col / 0.1)," & _
          " " & vbCr & "       TYPENAME(CURRENCY_col / 0.1), " & vbCr & "       TYPENAME(YESNO_col / 0.1), " & vbCr & "       TYPENAME(DATETIME_col / 0.1)," & vbCr & "       TYPENAME(TINYINT_col + 0.1), " & vbCr & "       TYPENAME(SMALLINT_col +" & _
          " 0.1), " & vbCr & "       TYPENAME(INTEGER_col + 0.1), " & vbCr & "       TYPENAME(REAL_col + 0.1), " & vbCr & "       TYPENAME(FLOAT_col + 0.1), " & vbCr & "       TYPENAME(DECIMAL_col + 0.1), " & vbCr & "       TYPENAME(CURRENCY_col" & _
          " + 0.1), " & vbCr & "       TYPENAME(YESNO_col + 0.1), " & vbCr & "       TYPENAME(DATETIME_col + 0.1)," & vbCr & "       TYPENAME(TINYINT_col - 0.1), " & vbCr & "       TYPENAME(SMALLINT_col - 0.1), " & vbCr & "       TYPENAME(INTEGER_col" & _
          " - 0.1), " & vbCr & "       TYPENAME(REAL_col - 0.1), " & vbCr & "       TYPENAME(FLOAT_col - 0.1), " & vbCr & "       TYPENAME(DECIMAL_col - 0.1), " & vbCr & "       TYPENAME(CURRENCY_col - 0.1), " & vbCr & "       TYPENAME(YESNO_col" & _
          " - 0.1), " & vbCr & "       TYPENAME(DATETIME_col - 0.1)" & vbCr & "FROM TestNumericDataTypes;"

      Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

The result is Decimal in every case. Q.E.D.

A few exceptions alluded to earlier:

Decimal literals which are equal to their INTEGER value e.g.

SELECT TYPENAME(1.0)

returns 'Long' (which is the VBA equivalent of Jet 4.0's INTEGER type -- why it shows the VBA name and not the Jet name I don't know).

...except when the value is beyond the INTEGER range:

SELECT TYPENAME(10000000000)

returns 'Decimal'

...excpet when the value is beyond the DECIMAL range:

SELECT TYPENAME(1E29)

returns 'Double' (being the VBA equivalent of Jet's FLOAT).

In the positive range, operating on the value with a DECIMAL literal retains the type as FLOAT e.g.

SELECT TYPENAME(1E29 + 0.1)

returns 'Double(FLOAT`).

...whereas in the negative range it is coersed to DECIMAL

SELECT TYPENAME(1E-29 + 0.1)

returns 'Decimal'.

Coersion works differently when crossing bounds e.g. (noting that the upper bounds for INTEGER is 2,147,483,647):

SELECT TYPENAME(2147483648)

returns 'Decimal`

...whereas:

SELECT TYPENAME(2147483647 + 1.0)

returns 'Double' (FLOAT).

No doubt there are other exceptions I haven't stumbled upon.

I'm having some difficulty parsing the difference between "extremely large or extremely large small amount" -- would that latter be be sufficiently small large amounts? ;) — David-W-Fenton, Oct 29 '10 at 23:35
What is the basis for your assertion that multipying by a decimal coerces the result to a decimal data type? I can't imagine that such an operation would behave differently from Jet 4 on (when decimal data type was introduced) as opposed to how it would have behaved before (when there was no decimal data type). — David-W-Fenton, Oct 29 '10 at 23:40
Quibbles aside, I agree with your point that something's fishy in that appropriate management of decimal accuracy has to happen at all the intervening steps in the calculation. It's a variation on what I said in my comment to your original answer. — David-W-Fenton, Oct 29 '10 at 23:41
@David-W-Fenton: "What is the basis for your assertion that multipying by a decimal coerces the result to a decimal data type?" see my update to this answer. I assume that after you have read it you will take back your comments from a few weeks ago that you never use the `DECIMAL` data type. The truth is, ACE/Jet uses it natively and if the `DECIMAL` type was as broken as you have been mislead to believe by a certain Mr Browne then the platform would be unuseable. Oh, wait... ;) — onedaywhen, Nov 01 '10 at 09:46
@David-W-Fenton:"extremely large small amount": Expand the result of `SELECT 1E-100` using non-scientific notation ('0.0000...1') and then tell me it isn't a large small number ;) — onedaywhen, Nov 01 '10 at 09:51
In Access 97/Jet 3.5, `SELECT TYPENAME(0.1)` returns `Double`. In the Immediate Window (i.e., VBA) in A2000/2003, `TYPENAME(0.1)` returns `Double`, while in a query, it returns `Decimal`, as you say. VBA and Jet 3.5 lack a decimal data type, so both return `Double`. Thus, it seems that you're right that the Jet 4.0 database engine changed how numbers to the right of the decimal place are handled. I'm considering what to think about this, but my initial reaction is that somebody should have made a bigger deal out of this significant change back in 1999. — David-W-Fenton, Nov 01 '10 at 20:53
Your DDL statement can be run in Access in SQL 92 mode. The SELECT you call DML is not a DML statement, but the second SQL statement in the block you call DDL is DML. You should correct that. — David-W-Fenton, Nov 01 '10 at 20:57
I think your "extremely large small amount" would be more clear if you just said "small decimal values" or "small fractional values with many digits" or something else. — David-W-Fenton, Nov 01 '10 at 20:59
I'm not sure what to think about all of this. It seems to me that it just shows exactly the same thing we already know from VBA, and that's to always operate on values of defined precision. That is, never have more than one literal value in a calculation, instead, calculate in stages and store in variables of the appropriate data types, then perform your operations with the variables. This is the advice that has been given for the rounding problem since forever, and this just seems to me to be a case of it in SQL, where you don't have variables, but can coerce to particular precisions. — David-W-Fenton, Nov 01 '10 at 21:02
@David-W-Fenton: "somebody should have made a bigger deal out of this significant change back in 1999" -- I've been making a big deal the Jet 4.0 changes for nearly that long. I blame the Access MVPs focusing on the negative: "Where's my DAO reference, dude?", "The Decimal type is unusable because I heard it had an obscure bug", and my favourite, "Why are you posting SQL Server syntax? Jet doesn't have `CHECK` constraints!" — onedaywhen, Nov 02 '10 at 06:17
@David-W-Fenton: "The SELECT you call DML is not a DML statement" -- SQL DML (data manipulation language) is an informal classification i.e. isn't defined by Standards. `SELECT` is usually classed as DML. What would you classify it as? — onedaywhen, Nov 02 '10 at 06:27
Well, what does the M in DML stand for? A SELECT does not manipulate the data, just retrieves it. I thought it was customary to use it only with SQL statements that modify data. — David-W-Fenton, Nov 02 '10 at 20:50
The DAO thing was a huge miscalculation on MS's part. They should have updated DAO in parallel to the database engine. They are paying for that stupid decision today, as are all of us who use Jet/ACE. — David-W-Fenton, Nov 02 '10 at 20:51
In the intitial release of Jet 4 and in the A2000, decimal data type WAS unusable. It took years before the problems were ironed out, and I'm not entirely convinced that they are yet fixed. — David-W-Fenton, Nov 02 '10 at 20:52
As to the focus of MVPs, most of them drank the Kool Aid and went all-ADO all-the-time, against all logic. A fundamental change like this really ought to be been foregrounded in all the documentation about the new features of Jet 4. So far as I know, it wasn't at all, but maybe I completely missed it because they buried in ADO documentation somewhere. — David-W-Fenton, Nov 02 '10 at 20:53

SQL round to the nearest hundredth

6 Answers6