DB2 10.5 for Linux, UNIX, and Windows

DECIMAL or DEC scalar function

The DECIMAL function returns a decimal representation of a number, a string representation of a number, or a datetime value.

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.

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

Example 1: 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
```
Example 2: 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
```
Example 3: 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.
Example 4: 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.
Example 5: 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.
Example 6: Assume that the column RECEIVED (whose data type is TIMESTAMP) has an internal value equivalent to '1988-12-22-14.07.21.136421'.
```
   DECIMAL(RECEIVED)
```
results in the value 19 881 222 140 721.136421.

Example 7: 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