SQL Language Guide > SQL Language Guide > Data Types > Data Types > Money Data Type
Was this helpful?
Money Data Type
The money data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.
Money columns can accommodate the following range of values:
$-999,999,999,999.99 to $999,999,999,999.99
A money value can be specified as either:
• A character string literal—The format for character string input of a money value is $sdddddddddddd.dd. The dollar sign is optional and the algebraic sign (s) defaults to + if not specified. There is no need to specify a cents value of zero (.00).
• A number—Any valid integer or floating point number is acceptable. The number is converted to the money data type automatically.
On output, money values display as strings of 20 characters with a default precision of two decimal places. The display format is:
$[-]dddddddddddd.dd
where:
$ is the default currency symbol
d is a digit from 0 to 9
The following settings affect the display of money data. For details, see the System Administrator Guide:
Variable
Description
II_MONEY_FORMAT
Specifies the character displayed as the currency symbol. The default currency sign is the dollar sign ($). II_MONEY_FORMAT also specifies whether the symbol appears before or after the amount.
II_MONEY_PREC
Specifies the number of digits displayed after the decimal point; valid settings are 0, 1, and 2.
II_DECIMAL
Specifies the character displayed as the decimal point; the default decimal point character is a period (.). II_DECIMAL also affects FLOAT, FLOAT4, and the DECIMAL data types.
Note:  If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
Last modified date: 06/28/2024