decimal(20,00) 1

[matrixindicator]

I have a number (invoice amount) with 2 numbers after the space. In my table definition I choice DECIMAL but he is using only one number after the comma. If I try to insert a number he gives an SQL error..

Q1 : Is decimal the best choice for a number with 2 numbers after the comma ?
Q2 : if so how can I set to use bij default 2 instead of 1 after numbers after the comma ?

 

[r937]

in this case you should use VARCHAR

it is unlikely you would ever want the sum of all invoice numbers, nor the average invoice number

if you're not going to do arithmetic on it, make it VARCHAR



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[matrixindicator]

ok varchar could be an easy step to solve this issue, but some doubt that this is for my situation the best.

Q1: why does there exist a data type lice DECIMAL if you can't set or specifie numbere behind the commma ?
Q2. In this case I need to do some arithmatic, or at least lets open the possibility (avg, total).

 

[r937]

DECIMAL(20,2) would allow a total of 20 numeric positions, of which 2 are to the right of the decimal point

DECIMAL(20,0) would ~not~ allow any numbers to the right of the decimal point

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vacunita]

You can use the decimal like you want to but you need to specify layout if you will when you create the table and define the fields in your DB:

Example:

CREATE TABLE `mydecimals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mydec` [blue]decimal(4,2)[/blue] DEFAULT NULL,
  `otherdec` [green]decimal(8,3)[/green] DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Like:

[blue]decimal (4,2)[/blue]: yields a decimal that has 4 digits with two of them on the right of the decimal point: 12.12

[green]decimal(8,3)[/green]: This would give you a number that has 8 digits. 3 of them being to the right of the decimal point: 12345.123





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[matrixindicator]

solved
DECIMAL(20,00) I was trying ,00
DECIMAL(20,2) = works