15.10 - FORMAT Phrase and NUMERIC Formats - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

The formatting characters in a FORMAT phrase determine whether the output of numeric data is considered to be monetary or non-monetary. Numeric information is considered to be monetary if the FORMAT phrase contains a currency symbol.

Formatting characters are case insensitive.

The result of a formatted numeric string is right‑justified.

Numeric information is considered to be non-monetary if the FORMAT phrase does not contain any currency or dual currency symbols. Use the following characters in the FORMAT phrase to control formatting of non-monetary numeric information:

 

Character

Meaning

G

Invokes the grouping rule defined by GroupingRule in the SDF.

The value of GroupSeparator in the SDF is copied to the output string to separate groups of digits to the left of the radix separator, according to the grouping rule defined by GroupingRule. Grouping applies to the integer portion of REAL, FLOAT, or DOUBLE PRECISION data.

The G must appear as the first character in a FORMAT phrase.

If the FORMAT phrase does not contain the letter G, no grouping is done on the output string.

The G cannot appear in a FORMAT phrase that contains any of the following characters:

  • ,
  • .
  • /
  • :
  • S
  • D

    Radix symbol.

    The value of RadixSeparator in the current SDF is copied to the output string whenever a D appears in the FORMAT phrase.

    The D cannot appear in a FORMAT phrase that contains any of the following characters:

  • .
  • ,
  • /
  • :
  • S
  • V
  • /

    :

    %

    Insertion characters.

    Copied to output string where they appear in the FORMAT phrase.

    The % insertion character cannot appear in a FORMAT phrase that contains S, and cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, G9999D99% is valid, but 9(9)D99%E999 is not.

    The / and : insertion characters cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • S
  • ,

    Grouping character.

    The comma is inserted only if a digit has already appeared.

    The comma is interpreted as the grouping character regardless of the value of GroupSeparator in the SDF.

    The comma cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • S
  • .

    Radix character.

    The period is interpreted as the radix character, regardless of the value of RadixSeparator in the SDF, and is copied to the output string.

    The period cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • V
  • S
  • B

    Insertion character.

    A blank is copied to the output string wherever a B appears in the FORMAT phrase.

    B cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, 999B99B9999 is valid, but G9(9)BD99 is not.

    +

    -

    Sign characters.

    Can appear at the beginning or end of a format string, but cannot appear between Z or 9 characters. One sign character places the edit character in a fixed position for the output string.

    If two or more of these characters are present, the sign floats (moves to the position just to the left of the number as determined by the stated structure). Repeated sign characters must appear to the left of a combination of the radix and any 9 formatting characters.

    If a group of repeated sign characters appears in a FORMAT phrase with a group of repeated Z characters, the group of Z characters must immediately follow the group of sign characters. For example, --ZZZ.

    One trailing sign character can occur to the right of any digits, and can combine with B. For example, G9(I)B+.

    The trailing sign character for a mantissa cannot appear to the right of the exponent. For example, 999D999E+999+ is not valid.

    The + translates to + or - as appropriate; the - translates to - or blank.

    V

    Implied decimal point position.

    Internally, the V is recognized as a decimal point to align the numeric value properly for calculation.

    Because the decimal point is implied, it does not occupy any space in storage and is not included in the output.

    V cannot appear in a FORMAT phrase that contains the D radix symbol or the . radix character.

    Z

    Zero‑suppressed decimal digit.

    Translates to blank if the digit is zero and preceding digits are also zero.

    A FORMAT phrase containing Z characters (including Z(I) and Z(F)), a combination of commas, dots, G, or D, and no other formatting characters means “blank when zero.”

    For example, ZZZZZ, ZZ.Z, GZ(I)DZ(F), GZZZZZZDZZ and Z,ZZZ.ZZ print only blanks if the number is zero.

    A Z cannot follow a 9.

    Repeated Z characters must appear to the left of any combination of the radix and any 9 formatting characters.

    The characters to the right of the radix cannot be a combination of 9 and Z characters; they must be all 9s or all Zs. If they are all Zs, then the characters to the left of the radix must also be all Zs.

    If a group of repeated Z characters appears in a FORMAT phrase with a group of repeated sign characters, the group of Z characters must immediately follow the group of sign characters. For example, --ZZZ.

    9

    Decimal digit (no zero suppress).

    CHAR(n)

    For more than one occurrence of a character, where CHAR can be:

  • - (sign character)
  • +
  • Z
  • 9
  • and n can be:

  • an integer constant
  • I
  • F
  • If n is F, CHAR can only be Z or 9.

    If n is an integer constant, the (n) notation means that the character repeats n number of times. For the meanings of I and F, see the definitions later in this table.

    I

    The number of characters needed to display the integer portion of numeric and integer data.

    I can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:

  • - (sign character)
  • +
  • Z
  • 9
  • CHAR(I) can only appear once, and is valid for the following types:

  • DECIMAL/NUMERIC
  • BYTEINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • The value of I is resolved during the formatting of the numeric data. The value is obtained from the declaration of the data type. For example, I is eight for the DECIMAL(10,2) type.

    If CHAR(F) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):

  • D
  • .
  • V
  • F

    The number of characters needed to display the fractional portion of numeric data.

    F can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:

  • Z
  • 9
  • CHAR(F) is valid for the DECIMAL/NUMERIC data type.

    The value of F is resolved during the formatting of the monetary numeric data. The value is obtained from the declaration of the data type. For example, F is two for the DECIMAL(10,2) type.

    A value of zero for F displays no fractional precision for the data; however, the value of RadixSeparator in the current SDF is copied to the output string if a D appears in the FORMAT phrase.

    CHAR(F) can appear only once. If CHAR(I) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):

  • D
  • .
  • V
  • -

    Dash character.

    Used when displaying numbers such as telephone numbers, social security numbers, and account numbers.

    A dash appears after the first digit and before the last digit, and is treated as an embedded dash rather than a sign character.

    A dash cannot follow any of these characters:

  • .
  • ,
  • +
  • G
  • D
  • E
  • S
  • V
  • U+6642

    U+5206

    U+79D2

    Kanji JI, FUN, and BYOU time markers.

    Each marker can appear only once.

    For more information on using Kanji time markers, see “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.

    S

    Signed Zoned Decimal character.

    Defines signed zoned decimal input as a numeric data type and displays numeric output as signed zone decimal character strings.

    S
    (
    continued)

    When converting signed zone decimal input to a numeric data type, the final character is converted according to the rules as follows:

  • Last character = { or 0, then the numeric conversion is n … 0
  • Last character = A or 1, then the numeric conversion is n … 1
  • Last character = B or 2, then the numeric conversion is n … 2
  • Last character = C or 3, then the numeric conversion is n … 3
  • Last character = D or 4, then the numeric conversion is n … 4
  • Last character = E or 5, then the numeric conversion is n … 5
  • Last character = F or 6, then the numeric conversion is n … 6
  • Last character = G or 7, then the numeric conversion is n … 7
  • Last character = H or 8, then the numeric conversion is n … 8
  • Last character = I or 9, then the numeric conversion is n … 9
  • Last character = }, then the numeric conversion is -n … 0
  • Last character = J, then the numeric conversion is -n … 1
  • Last character = K, then the numeric conversion is -n … 2
  • Last character = L, then the numeric conversion is -n … 3
  • Last character = M, then the numeric conversion is -n … 4
  • Last character = N, then the numeric conversion is -n … 5
  • Last character = O, then the numeric conversion is -n … 6
  • Last character = P, then the numeric conversion is -n … 7
  • Last character = Q, then the numeric conversion is -n … 8
  • Last character = R, then the numeric conversion is -n … 9
  • When displaying numeric output as signed zone decimal character strings, the final character indicates the sign, as follows:

  • If the final data digit is 0, then the final result digit is displayed as:
  • { if the result is a positive number
  • } if the result is a negative number
  • If the final data digit is 1, then the final result digit is displayed as:
  • A if the result is a positive number
  • J if the result is a negative number
  • If the final data digit is 2, then the final result digit is displayed as:
  • B if the result is a positive number
  • K if the result is a negative number
  • If the final data digit is 3, then the final result digit is displayed as:
  • C if the result is a positive number
  • L if the result is a negative number
  • If the final data digit is 4, then the final result digit is displayed as:
  • D if the result is a positive number
  • M if the result is a negative number
  • If the final data digit is 5, then the final result digit is displayed as:
  • E if the result is a positive number
  • N if the result is a negative number
  • S
    (
    continued)

  • If the final data digit is 6, then the final result digit is displayed as:
  • F if the result is a positive number
  • O if the result is a negative number
  • If the final data digit is 7, then the final result digit is displayed as:
  • G if the result is a positive number
  • P if the result is a negative number
  • If the final data digit is 8, then the final result digit is displayed as:
  • H if the result is a positive number
  • Q if the result is a negative number
  • If the final data digit is 9, then the final result digit is displayed as:
  • I if the result is a positive number
  • R if the result is a negative number
  • The S must follow the last decimal digit in the FORMAT phrase. It cannot appear in the same phrase with the following characters:

  • %
  • +
  • :
  • /
  • -
  • ,
  • .
  • D
  • G
  • Z
  • F
  • E
  • For examples and details on signed zone decimal conversion, see “Signed Zone Decimal Conversion” in SQL Functions, Operators, Expressions, and Predicates.

    E

    For exponential notation.

    Defines the end of the mantissa and the start of the exponent.

    The exponent consists of one optional + or - sign character followed by one or more 9 formatting characters.

    FN9

    Specifies a variable-length format that can be up to 64 characters as follows:

    1 If the value is negative, a - sign character is generated, otherwise a space is generated.

    2 The integral portion of the value is generated (without leading zeroes or blanks).

    3 If a fractional portion is present, the value of RadixSeparator in the SDF is generated followed by the minimum number of digits required to express the fractional portion.

    4 If the resulting string is longer than 64 characters, the value is formatted using the FNE format. This format is defined later in this table.

    FNE

    Specifies a variable-length exponential format as follows:

    1 The value is rounded, according to the rounding method of the underlying type, to a maximum of 38 digits precision.

    2 If the value is negative, a - sign character is generated, otherwise a space is generated.

    3 If a single digit precision is sufficient for the mantissa, that digit is generated. Otherwise the highest order digit is generated followed by the value of RadixSeparator in the SDF. Then the remaining mantissa digits, up to 37 digits, are generated.

    4 The E character is generated.

    5 The signed two or, if necessary, three digit exponent is generated.

    Numeric information is considered to be monetary if the FORMAT phrase contains a currency symbol. Use the characters in the following table in the FORMAT phrase to control formatting of monetary numeric information.

     

    Character

    Meaning

    G

    Invokes the currency grouping rule defined by CurrencyGroupingRule in the SDF.

    The value of CurrencyGroupSeparator in the current SDF is copied to the output string to separate groups of digits to the left of the currency radix separator, according to the currency grouping rule defined by CurrencyGroupingRule. Grouping applies to the integer portion of REAL, FLOAT, or DOUBLE PRECISION data.

    The G must appear as the first character in a FORMAT phrase. A currency character, such as L or C, must also appear in the FORMAT phrase.

    If the FORMAT phrase does not contain the letter G, no grouping is done on the output string.

    The G cannot appear in a FORMAT phrase that contains any of the following characters:

  • ,
  • .
  • /
  • :
  • S
  • /

    :

    %

    Insertion characters.

    Copied to output string where they appear in the FORMAT phrase.

    The % insertion character cannot appear in a FORMAT phrase that contains S, and cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, GL9999D99% is valid, but L9(9)D99%E999 is not.

    The / and : insertion characters cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • S
  • B

    Insertion character.

    A blank is copied to the output string wherever a B appears in the FORMAT phrase.

    B cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, GNB99D99 is valid, but G9(9)BD99 is not.

    +

    -

    Sign characters.

    These characters can appear at the beginning or end of a format string, but cannot appear between Z or 9 characters, or between repeated currency characters. One sign character places the edit character in a fixed position for the output string.

    If two or more of these characters are present, the sign floats (moves to the position just to the left of the number as determined by the stated structure). Repeated sign characters must appear to the left of formatting characters consisting of a combination of the radix and any 9 formatting characters.

    If a group of repeated sign characters appears in a FORMAT phrase with a group of repeated Z characters or a group of repeated currency characters or both, the groups must be contiguous. For example, +++$$$ZZZ.

    One trailing sign character can occur to the right of any digits, and can combine with B and one currency character or currency sign. For example, G9(I)B+L.

    The trailing sign character for a mantissa cannot appear to the right of the exponent. For example, 999D999E+999+ is invalid.

    The + translates to + or - as appropriate; the - translates to - or blank.

    $

    £

    ¥

    ¤

    ¤

    Currency signs:

  • $ means Dollar sign.
  • £ means Pound sterling.
  • ¥ means Yen sign.
  • ¤ means general currency sign.
  • ¤ means Euro sign.
  • A currency sign cannot appear between Z or 9 formatting characters, or between repeated sign characters.

     

    One currency sign places the edit character in a fixed position for the output string.

    If a result is formatted using a single currency sign with Zs for zero‑suppressed decimal digits (for example, £ZZ9.99), blanks can occur between the currency sign and the leftmost nonzero digit of the number.

    If the same currency sign appears more than once, the sign floats to the right, leaving no blanks between it and the leftmost digit.

    A currency sign cannot appear in the same phrase with a currency character, such as L.

    If + or - is present, the currency character cannot precede it.

    If a group of repeated currency signs appears in a FORMAT phrase with a group of repeated sign characters or a group of repeated Z characters or both, the groups must be contiguous. For example, +++$$$ZZZ.

    One currency sign can occur to the right of any digits, and can combine with B and one trailing sign character. For example, G9(I)B+$.

    A currency sign cannot appear to the right of an exponent. For example, 999D999E+999B+$ is invalid.

    L

    C

    N

    O

    U

    A

    Currency characters.

    The value of the corresponding currency string in the current SDF is copied to the output string whenever the character appears in the FORMAT phrase.

  • L in a FORMAT phrase is interpreted as the currency symbol and the value of the Currency string in the SDF is copied to the output string.
  • C in a FORMAT phrase is interpreted as the ISO currency symbol and the value of the ISOCurrency string in the SDF is copied to the output string.
  • N in a FORMAT phrase is interpreted as the full currency name, such as Yen or Kroner, and the value of the CurrencyName string in the SDF is copied to the output string.
  • O in a FORMAT phrase is interpreted as the dual currency symbol and the value of the DualCurrency string in the SDF is copied to the output string.
  • U in a FORMAT phrase is interpreted as the dual ISO currency symbol and the value of the DualISOCurrency string in the SDF is copied to the output string.
  • A in a FORMAT phrase is interpreted as the full dual currency name, such as Euro, and the value of the DualCurrencyName string in the SDF is copied to the output string.
  • A currency character cannot appear between Z or 9 formatting characters, or between repeated sign characters.

    If the same currency character appears more than once, the value that is copied to the output string floats to the right, leaving no blanks between it and the leftmost digit. Repeated characters must be contiguous, and must appear to the left formatting characters consisting of a combination of the radix and any 9 formatting characters.

    If a group of repeated currency characters appears in a FORMAT phrase with a group of repeated sign characters or a group of repeated Z characters or both, the groups must be contiguous. For example, +++LLLZZZ.

     

    A currency character cannot appear in the same phrase with any of the following characters:

  • other currency characters
  • a currency sign, such as $ or £
  • ,
  • .
  • One currency character can occur to the right of any digits, and can combine with B and one trailing sign character. For example, G9(I)B+L.

    A currency character cannot appear to the right of an exponent. For example, 999D999E+999B+L is invalid.

    V

    Implied decimal point position.

    Internally, the V is recognized as a decimal point to align the numeric value properly for calculation.

    Because the decimal point is implied, it does not occupy any space in storage and is not included in the output.

    V cannot appear in a FORMAT phrase that contains the D radix symbol or the . radix character.

    Z

    Zero‑suppressed decimal digit.

    Translates to blank if the digit is zero and preceding digits are also zero.

    A Z cannot follow a 9.

    Repeated Z characters must appear to the left of any combination of the radix and any 9 formatting characters.

    The characters to the right of the radix cannot be a combination of 9 and Z characters; they must be all 9s or all Zs. If they are all Zs, then the characters to the left of the radix must also be all Zs.

    If a group of repeated Z characters appears in a FORMAT phrase with a group of repeated sign characters, the group of Z characters must immediately follow the group of sign characters. For example, --ZZZ.

    9

    Decimal digit (no zero suppress).

    E

    For exponential notation.

    Defines the end of the mantissa and the start of the exponent.

    The exponent consists of one optional + or - sign character followed by one or more 9 formatting characters.

    CHAR(n)

    For more than one occurrence of a character, where CHAR can be one of the following:

  • - (sign character)
  • +
  • Z
  • 9
  • $
  • ¤
  • ¤
  • ¥
  • £
  • and n can be:

  • an integer constant
  • I
  • F
  • If n is F, CHAR can only be Z or 9.

    If n is an integer constant, the (n) notation means that the character repeats n number of times. For the meanings of I and F, see the definitions later in this table.

    ,

    Currency grouping character.

    The comma is inserted only if a digit has already appeared.

    The comma is interpreted as the currency grouping character regardless of the value of the CurrencyGroupSeparator in the SDF.

    The comma cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • L
  • C
  • O
  • U
  • N
  • A
  • S
  • .

    Currency radix character.

    The period is interpreted as the currency radix character, regardless of the value of the CurrencyRadixSeparator in the SDF, and is copied to the output string.

    The period cannot appear in a FORMAT phrase that contains any of the following characters:

  • G
  • D
  • L
  • V
  • C
  • O
  • U
  • N
  • A
  • S
  • D

    Radix symbol.

    The value of CurrencyRadixSeparator in the current SDF is copied to the output string whenever a D appears in the FORMAT phrase.

    A currency symbol, such as a dollar sign or yen sign, must also appear in the FORMAT phrase.

     

    The D cannot appear in a FORMAT phrase that contains any of the following characters:

  • ,
  • .
  • /
  • :
  • S
  • V
  •  

    I

    The number of characters needed to display the integer portion of numeric and integer data.

    I can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:

  • - (sign character)
  • +
  • Z
  • 9
  • $
  • ¤
  • ¤
  • ¥
  • £
  • CHAR(I) can only appear once, and is valid for the following types:

  • DECIMAL/NUMERIC
  • BYTEINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • The value of I is resolved during the formatting of the monetary numeric data. The value is obtained from the declaration of the data type. For example, I is eight for the DECIMAL(10,2) type.

    If CHAR(F) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):

  • D
  • .
  • V
  • F

    The number of characters needed to display the fractional portion of numeric data.

    F can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:

  • Z
  • 9
  • CHAR(F) is valid for the DECIMAL/NUMERIC data type.

    The value of F is resolved during the formatting of the monetary numeric data. The value is obtained from the declaration of the data type. For example, F is two for the DECIMAL(10,2) type.

    A value of zero for F displays no fractional precision for the data; however, the value of CurrencyRadixSeparator in the current SDF is copied to the output string if a D appears in the FORMAT phrase.

     

    CHAR(F) can appear only once. If CHAR(I) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):

  • D
  • .
  • V
  • -

    Dash character.

    Used when storing numbers such as telephone numbers, social security numbers, and account numbers.

    A dash appears after the first digit and before the last digit, and is treated as an embedded dash rather than a sign character. A dash cannot follow any of these characters:

  • .
  • ,
  • +
  • G
  • N
  • A
  • C
  • L
  • O
  • U
  • D
  • V
  • S
  • E
  • $
  • ¤
  • £
  • ¥
  • ¤
  • S

    Signed Zoned Decimal character.

    Defines signed zoned decimal input as a numeric data type and displays numeric output as signed zone decimal character strings.

    When converting signed zone decimal input to a numeric data type, the final character is converted as follows:

  • Last character = { or 0, then the numeric conversion is n … 0
  • Last character = A or 1, then the numeric conversion is n … 1
  • Last character = B or 2, then the numeric conversion is n … 2
  • Last character = C or 3, then the numeric conversion is n … 3
  • Last character = D or 4, then the numeric conversion is n … 4
  • Last character = E or 5, then the numeric conversion is n … 5
  • Last character = F or 6, then the numeric conversion is n … 6
  • Last character = G or 7, then the numeric conversion is n … 7
  • Last character = H or 8, then the numeric conversion is n … 8
  • Last character = I or 9, then the numeric conversion is n … 9
  • Last character = }, then the numeric conversion is -n … 0
  • Last character = J, then the numeric conversion is -n … 1
  • Last character = K, then the numeric conversion is -n … 2
  • Last character = L, then the numeric conversion is -n … 3
  • Last character = M, then the numeric conversion is -n … 4
  • S
    (
    continued)

  • Last character = N, then the numeric conversion is -n … 5
  • Last character = O, then the numeric conversion is -n … 6
  • Last character = P, then the numeric conversion is -n … 7
  • Last character = Q, then the numeric conversion is -n … 8
  • Last character = R, then the numeric conversion is -n … 9
  • When displaying numeric output as signed zone decimal character strings, the final character indicates the sign, as follows:

  • If the final data digit is 0, then the final result digit is displayed as:
  • { if the result is a positive number
  • } if the result is a negative number
  • If the final data digit is 1, then the final result digit is displayed as:
  • A if the result is a positive number
  • J if the result is a negative number
  • If the final data digit is 2, then the final result digit is displayed as:
  • B if the result is a positive number
  • K if the result is a negative number
  • If the final data digit is 3, then the final result digit is displayed as:
  • C if the result is a positive number
  • L if the result is a negative number
  • If the final data digit is 4, then the final result digit is displayed as:
  • D if the result is a positive number
  • M if the result is a negative number
  • If the final data digit is 5, then the final result digit is displayed as:
  • E if the result is a positive number
  • N if the result is a negative number
  • If the final data digit is 6, then the final result digit is displayed as:
  • F if the result is a positive number
  • O if the result is a negative number
  • If the final data digit is 7, then the final result digit is displayed as:
  • G if the result is a positive number
  • P if the result is a negative number
  • If the final data digit is 8, then the final result digit is displayed as:
  • H if the result is a positive number
  • Q if the result is a negative number
  • If the final data digit is 9, then the final result digit is displayed as:
  • I if the result is a positive number
  • R if the result is a negative number
  • The S must follow the last decimal digit in the FORMAT phrase. It cannot appear in the same phrase with the following characters.

    S
    (
    continued)

  • %
  • +
  • :
  • /
  • -
  • ,
  • .
  • Z
  • E
  • D
  • G
  • F
  • N
  • A
  • C
  • L
  • U
  • O
  • $
  • ¤
  • £
  • ¥
  • ¤
  •  

    For examples and details on signed zone decimal conversion, see “Signed Zone Decimal Conversion” in SQL Functions, Operators, Expressions, and Predicates.

    A FORMAT phrase that defines fewer positions than are required by numeric values causes the data to be returned as follows:

  • Asterisks appear when the integer portion cannot be accommodated.
  • When only the integer portion can be accommodated, any digits to the right of the least significant digit are either truncated (for an integer value) or rounded (for a floating, number, or decimal value).
  • Rounding is based on “Round to the Nearest” mode, as illustrated by the following process.

    1 Let B represent the actual result.

    2 Let A and C represent the nearest bracketing values that can be represented, such that A < B < C.

    3 The determination as to whether A or C is the represented result is made as follows

    a When possible, the result is the value nearest to B.

    b If A and C are equidistant (for example, the fractional part is exactly .5), the result is the even number.

    The following SELECT statement returns 48 and 48.

       SELECT 47.5(FORMAT 'zzzz'), 48.5(FORMAT('zzzz')) ;

    A FORMAT clause can cause the number to be rounded; for example, consider the following:

       SELECT 1.3451 (FORMAT 'zz.z');

    The FORMAT clause operates on 1.3451 and returns 1.3.

       SELECT 13451 / 10000.000

    1.345 is returned as expected.

       SELECT 13451 / 10000.000 (FORMAT 'zz.z');

    The arithmetic expression is evaluated first (yielding 1.345), then the FORMAT clause is applied, returning 1.3.

       SELECT 13451 / 10000.00 (FORMAT 'zz.z');

    Note that two ‘roundings’ occur; first to 1.35 when evaluating the expression, then a second rounding to 1.4 as a result of the FORMAT statement.

    To avoid double ‘roundings’, extend the precision of the arithmetic expression two decimal places beyond that of the FORMAT clause.

    The display results of various FORMAT phrases for numeric data appear in the following table. For FORMAT phrases that contain G, D, L, and N formatting characters, assume that the related entries in the SDF are:

    RadixSeparator {","}
    GroupSeparator {"."}
    GroupingRule   {"3"}
    Currency       {"$"}
    CurrencyName   {"US Dollars"}
     

    FORMAT Phrase

    Data

    Result

    '$$9.99'

    .069

    $0.07

    '$$9.99'

    1095

    ******

    'ZZ,ZZ9.99'

    1095

    1,095.00

    '9.99E99'

    1095

    1.10E03

    '999V99'

    128.457

    128.46

    '$(5).9(2)'

    1

    $1.00

    '999-9999'

    8278777

    827‑8777

    'ZZ,ZZ9.99-'

    1095

    1,095.00

    'ZZ,ZZ9.99-'

    -1095

    1,095.00-

    '99999S'

    -1095

    0109N

    '99999S'

    1095

    0109E

    '--(8)D9(2)'

    0034567890

    34567890,00

    'G--(8)D9(2)'

    -12345678.90

    -12.345.678,90

    'Z(I)D9(F)'

    000000.42

    ,42

    'G-(10)9'

    1234567890

    1.234.567.890

    '-9D99999999999999E-999'

    1.74524064372835 e-2

    1,74524064372835 e-002

    'GLLZ(I)D9(F)'

    9988.77

    $9.988,77

    '-Z(I)BN'

    998877.66

    998878 US Dollars

    If the FORMAT phrase does not include a sign character or a signed zoned decimal character, then the sign for a negative value is discarded and the output is displayed as a positive number.