DB2 Version 9.7 for Linux, UNIX, and Windows

# DECIMAL or DEC scalar function

Numeric to Decimal:

>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'

>--(--numeric-expression--+----------------------------+--)----><
'-,--precision--+----------+-'
'-,--scale-'

String to Decimal:

>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'

>--(--string-expression--+------------------------------------------------------+--)-><
'-,--precision--+------------------------------------+-'
'-,--scale--+----------------------+-'
'-,--decimal-character-'

Datetime to Decimal:

>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'

>--(--datetime-expression--+-------------------------------+---><
'-,--precision--+----------+--)-'
'-,--scale-'

The schema is SYSIBM.

The DECIMAL function returns a decimal representation of:
• A number
• A string representation of a number
• A datetime value
Numeric to Decimal
numeric-expression
An expression that returns a value of any built-in numeric data type.
precision
An integer constant with a value in the range of 1 to 31.
The default for precision depends on the data type of numeric-expression:
• 31 for decimal floating-point
• 15 for floating-point and decimal
• 19 for big integer
• 11 for large integer
• 5 for small integer.
scale
An integer constant in the range of 0 to the precision value. The default is zero.

The result is the same number that would occur if the first argument were assigned to a decimal column or variable with a precision of precision and a scale of scale. Digits are truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale scale. An error is returned if the number of significant decimal digits required to represent the whole part of the number is greater than precision - scale (SQLSTATE 22003).

String to Decimal
string-expression
An expression that returns a value that is a character string or a Unicode graphic-string representation of a number with a length not greater than the maximum length of a character constant. The data type of string-expression must not be CLOB or DBCLOB (SQLSTATE 42884). Leading and trailing blanks are eliminated from the string and the resulting string must conform to the rules for forming an integer, decimal, floating-point, or decimal floating-point constant (SQLSTATE 22018).

The string-expression is converted to the section code page if required to match the code page of the constant decimal-character.

precision
An integer constant with a value in the range 1 to 31 that specifies the precision of the result. If not specified, the default is 15.
scale
An integer constant with a value in the range 0 to precision that specifies the scale of the result. If not specified, the default is 0.
decimal-character
Specifies the single-byte character constant used to delimit the decimal digits in string-expression from the whole part of the number. The character cannot be a digit, plus (+), minus (-), or blank, and it can appear at most once in string-expression (SQLSTATE 42815).

The result is the same number that would result from CAST(string-expression AS DECIMAL(precision, scale)). Digits are truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale scale. An error is returned if the number of significant digits to the left of the decimal character (the whole part of the number) in string-expression is greater than precision - scale (SQLSTATE 22003). The default decimal character is not valid in the substring if a different value for the decimal-character argument is specified (SQLSTATE 22018).

Datetime to Decimal
datetime-expression
An expression that returns a value of type DATE, TIME or TIMESTAMP.
precision
An integer constant with a value in the range 1 to 31 that specifies the precision of the result. If not specified, the default for the precision and scale depends on the data type of datetime-expression as follows:
• Precision is 8 and scale is 0 for a DATE. The result is a DECIMAL(8,0) value representing the date as yyyymmdd.
• Precision is 6 and scale is 0 for a TIME. The result is a DECIMAL(6,0) value representing the time as hhmmss.
• Precision is 14+tp and scale is tp for a TIMESTAMP(tp). The result is a DECIMAL(14+tp,tp) value representing the timestamp as yyyymmddhhmmss.nnnnnnnnnnnn.
scale
An integer constant with a value in the range 0 to precision that specifies the scale of the result. If not specified and a precision is specified, the default is 0.

The result is the same number that would result from CAST(datetime - expression AS DECIMAL(precision, scale)). Digits are truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale scale. An error is returned if the number of significant digits to the left of the decimal character (the whole part of the number) in string-expression is greater than precision - scale (SQLSTATE 22003).

If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Note: The CAST specification should be used to increase the portability of applications. For more information, see "CAST specification".

## Examples

• Use the DECIMAL function in order to force a DECIMAL data type (with a precision of 5 and a scale of 2) to be returned in a select-list for the EDLEVEL column (data type = SMALLINT) in the EMPLOYEE table. The EMPNO column should also appear in the select list.
SELECT EMPNO, DECIMAL(EDLEVEL,5,2)
FROM EMPLOYEE
• Assume the host variable PERIOD is of type INTEGER. Then, in order to use its value as a date duration it must be "cast" as decimal(8,0).
SELECT PRSTDATE + DECIMAL(:PERIOD,8)
FROM PROJECT
• Assume that updates to the SALARY column are input through a window as a character string using comma as a decimal character (for example, the user inputs 21400,50). Once validated by the application, it is assigned to the host variable newsalary which is defined as CHAR(10).
UPDATE STAFF
SET SALARY = DECIMAL(:newsalary, 9, 2, ',')
WHERE ID = :empid;
The value of newsalary becomes 21400.50.
• Add the default decimal character (.) to a value.
DECIMAL('21400,50', 9, 2, '.')
This fails because a period (.) is specified as the decimal character, but a comma (,) appears in the first argument as a delimiter.
• Assume that the column STARTING (whose data type is TIME) has an internal value equivalent to '12:10:00'.
DECIMAL(STARTING)
results in the value 121 000.
• Assume that the column RECEIVED (whose data type is TIMESTAMP) has an internal value equivalent to '1988-12-22-14.07.21.136421'.
results in the value 19 881 222 140 721.136421.
• This example shows the decimal result and resulting precision and scale for various datetime input values. Assume the existence of the following columns with associated values:
Column name Data type Value
ENDDT DATE 2000-03-21
ENDTM TIME 12:02:21
ENDTS TIMESTAMP 2000-03-21-12.02.21.123456
ENDTS0 TIMESTAMP(0) 2000-03-21-12.02.21
ENDTS9 TIMESTAMP(9) 2000-03-21-12.02.21.123456789
The following table shows the decimal result and resulting precision and scale for various datetime input values.
DECIMAL(arguments) Precision and Scale Result
DECIMAL(ENDDT) (8,0) 20000321.
DECIMAL(ENDDT, 10) (10,0) 20000321.
DECIMAL(ENDDT, 12, 2) (12,2) 20000321.00
DECIMAL(ENDTM) (6,0) 120221.
DECIMAL(ENDTM, 10) (10,0) 120221.
DECIMAL(ENDTM, 10, 2) (10,2) 120221.00
DECIMAL(ENDTS) (20, 6) 20000321120221.123456
DECIMAL(ENDTS, 23) (23, 0) 20000321120221.
DECIMAL(ENDTS, 23, 4) (23, 4) 20000321120221.1234
DECIMAL(ENDTS0) (14,0) 20000321120221.
DECIMAL(ENDTS9) (23,9) 20000321120221.123456789