MySQL 4.1

I have created a table through EMS in my MySQL 4.1. One of the datafield,
Amount, is a type of Double for Size=15 and Precision=2. When I issue an
Insert statement, the data in the Amount filed is truncated.

For example,

if Amount = '16,000.00', it will be stored as 16.000 in the DB.

if Amount = '1,000,000.00', it will be saved as 1.000 in the DB.

INSERT INTO Reservation(ResvNum, Amount, RespDate) VALUES('W15Q05T0918',
'1,000,000.00', '05/20/2005')

Any pointers? TIA.


....Joe

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MySQL 4.1

--=_alternative 006DB4FC85257003_=
Content-Type: text/plain; charset="US-ASCII"

"Sheng, Joseph Y [AMSTA-AQ-AP]" wrote on
05/16/2005 02:50:43 PM:

>
> I have created a table through EMS in my MySQL 4.1. One of the
datafield,
> Amount, is a type of Double for Size=15 and Precision=2. When I issue an
> Insert statement, the data in the Amount filed is truncated.
>
> For example,
>
> if Amount = '16,000.00', it will be stored as 16.000 in the DB.
>
> if Amount = '1,000,000.00', it will be saved as 1.000 in the DB.
>
> INSERT INTO Reservation(ResvNum, Amount, RespDate) VALUES('W15Q05T0918',
> '1,000,000.00', '05/20/2005')
>
> Any pointers? TIA.
>
>
> ...Joe
>

1 - numbers are not strings. They do not need to be quoted.
2 - numbers do not contain commas. That is something we humans do to make
it easier to read them out loud. So instead of saying

INSERT tablename (datafield) values ('1,000,000.00')

SAY instead

INSERT tablename (datafield) values (1000000.00);

Does that make sense? Also, some languages (locales) use the comma AS THE
DECIMAL SEPARATOR. In those languages the first 4 digits of pi would be
written as 3,142. You are better of using the least possible puncutation
to represent your numbers as possible.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 006DB4FC85257003_=--

Re: MySQL 4.1

Hi,
As shawn said and to let you see, you meet a string conversion.

This an example and i let you find the conclusion :

mysql> insert into dbl values ('16,000.00');
mysql> insert into dbl values ('16This is another text');
mysql> insert into dbl values (16000.00);

mysql> select * from dbl;
+----------+
| t |
+----------+
| 16.00 |
| 16.00 |
| 16000.00 |
+----------+
3 rows in set (0.01 sec)

seen ?

Mathias

Selon SGreen@unimin.com:

> "Sheng, Joseph Y [AMSTA-AQ-AP]" wrote on
> 05/16/2005 02:50:43 PM:
>
> >
> > I have created a table through EMS in my MySQL 4.1. One of the
> datafield,
> > Amount, is a type of Double for Size=15 and Precision=2. When I issue an
> > Insert statement, the data in the Amount filed is truncated.
> >
> > For example,
> >
> > if Amount = '16,000.00', it will be stored as 16.000 in the DB.
> >
> > if Amount = '1,000,000.00', it will be saved as 1.000 in the DB.
> >
> > INSERT INTO Reservation(ResvNum, Amount, RespDate) VALUES('W15Q05T0918',
> > '1,000,000.00', '05/20/2005')
> >
> > Any pointers? TIA.
> >
> >
> > ...Joe
> >
>
> 1 - numbers are not strings. They do not need to be quoted.
> 2 - numbers do not contain commas. That is something we humans do to make
> it easier to read them out loud. So instead of saying
>
> INSERT tablename (datafield) values ('1,000,000.00')
>
> SAY instead
>
> INSERT tablename (datafield) values (1000000.00);
>
> Does that make sense? Also, some languages (locales) use the comma AS THE
> DECIMAL SEPARATOR. In those languages the first 4 digits of pi would be
> written as 3,142. You are better of using the least possible puncutation
> to represent your numbers as possible.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org