datatype change from number to money..implications

[eja2000]

what are the implications of changing a field with datatype of NUMBER to a datatype of MONEY or DECIMAL..their is data in the fields already..


i want to be able to receive decimal numbers and do simple arithmtic with them (eg addition, subtraction)

 

[vongrunt]

AFAIK... data type NUMBER doesn't exist in SQL2k. If you thought about NUMERIC, it is functionally equal to DECIMAL.

Money is basically fixed NUMERIC, with large enough range (+/- 922 trillion) and accuracy (4 decimal places) for most of financial operations.

 

[eja2000]

thanks..my mistake..i meant numeric..
does that mean i can put 4.20 in a numeric field? if i have 2o their and i add 4.20 what will i get?

 

[vongrunt]

When you create numeric/decimal fields, take care of precision (total number of digits) and scale (number of accurate decimals). Set these values according to greatest value and scale expected. For 20 + 4.20 = 24.20 result has 4 digits and 2 decimals (I included 0 intentionally), so numeric(4, 2) is minimum.

 

[eja2000]

i did not set any precision on the field..would it affect the data that is already in there?
the data i have is like 20,200,300,45 etc..
i want it able to receive numbers with 2 decimal places eg. 4.50,3.56 etc

thanks again..

 

[vongrunt]

Hm... without explicitely set precision/scale like:

alter table blah add foo numeric

... default is (18, 0) and decimals will be lost. Better check these columns with EM (double-click on table name).