ZONED
The ZONED function returns a zoned decimal representation.
Numeric to Zoned Decimal
String to Zoned Decimal
Datetime to Decimal
The ZONED function returns a zoned decimal representation of:
- A number
- A character or graphic string representation of a decimal number
- A character or graphic string representation of an integer
- A character or graphic string representation of a floating-point number
- A character or graphic string representation of a decimal floating-point number
- A date
- A time
- A timestamp
Numeric to Zoned Decimal
- numeric-expression
- An expression that returns a value of any built-in numeric data type.
- precision
- An integer constant with a value greater than or equal to 1 and
less than or equal to 63.
The default for precision depends on the data type of the numeric-expression:
- 5 for small integer
- 11 for large integer
- 19 for big integer
- 15 for floating point, decimal, numeric, or nonzero scale binary
- 31 for decimal floating point
- scale
- An integer constant that is greater than or equal to 0 and less than or equal to precision. If not specified, the default is 0.
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. 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. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
String to Zoned Decimal
- string-expression
- An expression that returns a value that is a character-string or graphic-string representation of a number. Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, decimal floating-point, integer, or decimal constant.
- precision
- An integer constant that is greater than or equal to 1 and less than or equal to 63. If not specified, the default is 15.
- scale
- An integer constant that is greater than or equal to 0 and less than or equal to precision. If not specified, the default is 0.
- decimal-character
- Specifies the single-byte character constant that was used to delimit the decimal digits in string-expression from the whole part of the number. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal separator character. For more information, see Decimal point.
The result is the same number that would result from CAST(string-expression AS NUMERIC(precision,scale)). Digits are truncated from the end if the number of digits to the right of the decimal-character is greater than the scale s. 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. The default decimal separator character is not valid in the substring if the decimal-character argument is specified.
Datetime to Zoned Decimal
- datetime-expression
- An expression that returns a value of type DATE, TIME, or TIMESTAMP.
- precision
- An integer constant that is greater than or equal to 1 and less
than or equal to 63 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 DATE. The result is a NUMERIC(8,0) value representing the date as yyyymmdd.
- Precision is 6 and scale is 0 for a TIME. The result is a NUMERIC(6,0) value representing the time as hhmmss.
- Precision is 14+tp and scale is tp for a TIMESTAMP(tp). The result is a NUMERIC(14+tp,tp) value representing the timestamp as yyyymmddhhmmss.nnnnnnnnnnnn.
- scale
- An integer constant that is greater than or equal to 0 and less than or equal to precision. 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 NUMERIC(precision,scale)). Digits are truncated from the end if the number of digits to the right of the decimal separator is greater than the scale s. An error is returned if the number of significant digits to the left of the decimal separator (the whole part of the number) in datetime-expression is greater than precision - scale.
The result of the function is a zoned decimal number with precision of precision and scale of scale. 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
Syntax alternatives: The CAST specification should be used to increase the portability of applications when the precision is specified. For more information, see CAST specification.
Examples
- Assume the host variable Z1 is a decimal host variable with a
value of 1.123.
Returns the value 1.12300000000000.SELECT ZONED(:Z1,15,14) FROM SYSIBM.SYSDUMMY1
- Assume the host variable Z1 is a decimal host variable with a
value of 1123.
Returns the value 1123.00.SELECT ZONED(:Z1,11,2) FROM SYSIBM.SYSDUMMY1
- Likewise,
Returns the value 1123.SELECT ZONED(:Z1,4) FROM SYSIBM.SYSDUMMY1