List:General Discussion« Previous MessageNext Message »
From:Matt Loschert Date:April 10 2003 7:44pm
Subject:rounding/truncation of decimal fields
View as plain text  
I have quick question about rounding and/or truncation of a decimal table
field.  I ran into this unexpectedly and found it surprising.

Given the following input:


  DROP TABLE IF EXISTS test1;
  CREATE TABLE test1 (id tinyint, truncated decimal(12,2));
  INSERT INTO test1 VALUES (1, '0.004');
  INSERT INTO test1 VALUES (2,  0.004 );
  INSERT INTO test1 VALUES (3, '0.005');
  INSERT INTO test1 VALUES (4,  0.005 );
  SELECT * FROM test1;
  SELECT VERSION();


I received this output.  Is this to be expected since I used a stringified
form for some of the numbers?


mysql> CREATE TABLE test1 (id tinyint, truncated decimal(12,2));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (1, '0.004');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (2,  0.004 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (3, '0.005');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4,  0.005 );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+-----------+
| id   | truncated |
+------+-----------+
|    1 |      0.00 |
|    2 |      0.00 |
|    3 |      0.00 |
|    4 |      0.01 |
+------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 3.23.49   |
+-----------+
1 row in set (0.00 sec)


- Matt

--
Matt Loschert - Software Engineer       | email: loschert@stripped    |
ServInt Internet Services               | web:   http://www.servint.net/ |
McLean, Virginia USA                    | phone: (703) 847-1381          |
Thread
rounding/truncation of decimal fieldsMatt Loschert10 Apr
  • Re: rounding/truncation of decimal fieldsgerald_clark11 Apr
RE: rounding/truncation of decimal fieldsClifton R [CC] Jones11 Apr
  • Re: rounding/truncation of decimal fieldsgerald_clark11 Apr
    • Re: rounding/truncation of decimal fieldsMatt Loschert11 Apr
      • Re: rounding/truncation of decimal fieldsDoug Thompson11 Apr
        • Re: rounding/truncation of decimal fieldsMatt Loschert12 Apr
          • Re: rounding/truncation of decimal fieldsDoug Thompson12 Apr
            • More on enumMartin Gainty12 Apr
            • Re: rounding/truncation of decimal fieldsMatt Loschert13 Apr
              • Re: rounding/truncation of decimal fieldsDoug Thompson13 Apr
              • Re: rounding/truncation of decimal fieldsDan Nelson13 Apr
                • Re: rounding/truncation of decimal fieldsMatt Loschert14 Apr
Re: rounding/truncation of decimal fieldsMoon79611 Apr