All geek questions in one place

SQL is rounded to the nearest hundredth

UPDATE prodfeatures2 SET featureprice = featureprice * 0.6316;

I'm trying to tune the round to the nearest hundredth. HELP!

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

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

+3
source share
6 answers
UPDATE prodfeatures2 SET featureprice = ROUND(featureprice * 0.6316,2)
+4
source

I do not think that you have provided enough data to determine which rounding algorithm you require.

If your specification tells you which rounding algorithm to use, please post it.

If your spec cannot tell you which rounding algorithm will then raise the problem with the designer.

, SQL . . , DECIMAL , .

+2

: MS Access, VBA

:

" Round , ". --Microsoft

.

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

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

:

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

ROUND(TheValue, 2)

+1

( (104.7668 (6,2)), 2) roundof;

+1

MS-Access: - !

featureprice , , , , 0.6316, , DECIMAL. , , DECIMAL Access (ACE, Jet, ), , :

SELECT CAST(104.7668 AS DECIMAL(17, 2)

104.76... , Access, SQL Standard, CDEC() hasn ' t ACE ( ). :

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

, prodfeatures2 CURRENCY, , , , DECIMAL, , , SQL .

, , , . :

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

, dp , dp? , - , , ... , .

, ?

( ). , ACE/Jet. , . Jet 4.0, .

( , , ) DECIMAL. :

SELECT TYPENAME(0.1)

"".

(, , ) DECIMAL DECIMAL ( , ).

, - , (, 1) , /// (, 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;

, Access, , SQL DDL, VBA ( , , Excel, , , ):

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

DECIMAL. Q.E.D.

, :

, INTEGER, .

SELECT TYPENAME(1.0)

"Long" ( VBA Jet 4.0 INTEGER - VBA, Jet, ).

... , INTEGER:

SELECT TYPENAME(10000000000)

'Decimal'

... excpet, DECIMAL:

SELECT TYPENAME(1E29)

'Double' ( VBA Jet FLOAT).

, DECIMAL, FLOAT, .

SELECT TYPENAME(1E29 + 0.1)

" ( FLOAT`).

... DECIMAL

SELECT TYPENAME(1E-29 + 0.1)

"".

- , . (, INTEGER 2 147 483 647):

SELECT TYPENAME(2147483648)

'Decimal`

... :

SELECT TYPENAME(2147483647 + 1.0)

"" (FLOAT).

, , .

0

Source: https://habr.com/ru/post/1771850/

More articles:


All Articles