USING

Syntax

expr USING format

expr is a language expression.
format is a string expression that defines the formatting mask to be used.

Usage

The USING operator applies a formatting string to the left operand.

The left operand must be a valid date, integer or decimal number. Note that DATETIME and INTERVAL expressions cannot be formatted with the USING operator.

The format operand can be any valid string expression using formatting characters as described in Formatting symbols for numbers and Formatting symbols for dates.

The USING operator has a low order of precedence: if you use operators with a higher precedence, the resulting string might not be what you are expecting.

For example, the || concatenation operator is evaluated before USING. As a result:

LET x = a || b USING "format"

will first concatenate a and b, then apply the USING format.

To solve this issue, use braces around the USING expression:

LET x = a || (b USING "format")

Formatting symbols for numbers

For numeric types such as INTEGER, FLOAT, DECIMAL, and especially MONEY, format-string consists of a set of place holders that represent digits, currency symbols, thousands and decimal separators. For example, "###.##@" defines three places to the left of the decimal point and exactly two to the right, plus a currency symbol at the end of the string. The USING operator is required to display the thousands separator defined in DBFORMAT.

When used with numeric values, the format-string must use normalized placeholders described in Table 1. Some of these wildcard characters will be replaced by digits, blanks or by the elements defined in the DBMONEY or DBFORMAT environment variables. Any other character will be interpreted as a literal, and can be used at any place in the format string. If the numeric value is too large to fit in the number of characters defined by the format, the result string is filled with a set of star characters (********) indicating an overflow.

Table 1. Format-string symbols for Numeric data types
Character	Description
`*`	The star placeholder fills with asterisks any position that would otherwise be blank.
`&`	The ampersand placeholder is used to define the position of a digit, and is replaced by a zero if that position would otherwise be blank.
`#`	The sharp placeholder is used to define the position of a digit, it is used to specify a maximum width for the resulting string. This wildcard character does not change any blank positions in the display: The character is replaced by a blank if no digit is to be displayed at that position.
`<`	Consecutive less than characters cause left alignment and define digit positions.
`-`	Displays a minus sign or a blank at that position. `USING` displays a minus sign when the expression is lower than zero, and otherwise a blank character. When you group several minus signs in the format string, a single minus sign floats immediately to the left of the displayed number.
`+`	Displays a plus or minus sign at that position. `USING` displays a plus sign when the expression is greater than or equal to zero, and a minus sign when the value is less than zero. When you group several plus signs in the format string, a single plus sign floats immediately to the left of the displayed number.
`(`	Displayed as left parenthesis for negative numbers. It is used to display accounting parentheses instead of a minus sign for negative numbers. Consecutive left parentheses display a single left parenthesis to the left of the number being printed.
`)`	Displayed as right parenthesis for negative numbers. This wildcard character is used in conjunction with a open brace to display accounting parentheses for negative numbers.
`,` (comma)	The comma placeholder is used to define the position for the thousand separator defined in DBFORMAT. The thousand separator will only be displayed if there is a number on the left of it.
`.` (period)	The period placeholder is used to define the position for the decimal separator defined in DBMONEY or DBFORMAT. You can only have one decimal separator in a number format string.
`$`	The dollar sign is the placeholder for the front currency symbol defined in DBMONEY or DBFORMAT. When you group several consecutive dollar signs, a single front currency symbol floats immediately to the left of the number being printed. The front currency symbol can be defined in DBFORMAT with more than one character.
`@`	The at sign is the placeholder for the back currency symbol defined in DBMONEY or DBFORMAT. Put several consecutive @ signs at the end of the format string to display a currency symbol defined in DBFORMAT with more than one character.

Table 2. Numeric formatting examples
Format String	Numeric value	DBFORMAT	Result string
`[######.##]`	`0`	`:.:,:`	`[ , ]`
`[######.##]`	`-1234.56`	`:.:,:`	`[ 1234,56]`
`[######.##]`	`-1234567.89`	`:.:,:`	`[*********]`
`[######.##]`	`+1234.56`	`:.:,:`	`[ 1234,56]`
`[#####&.&&]`	`0`	`:.:,:`	`[ 0,00]`
`[****.]`	`0`	`:.:,:`	`[******,00]`
`[****.]`	`-12.34`	`:.:,:`	`[****12,34]`
`[****.]`	`+12.34`	`:.:,:`	`[****12,34]`
`[<<<<<<.<<]`	`+12.34`	`:.:,:`	`[12,34]`
`[<<<<<<.<<]`	`-12.34`	`:.:,:`	`[12,34]`
`[---,--&.&&]`	`-1234.56`	`:.:,:`	`[ -1.234.56]`
`[+++,++&.&&]`	`-1234.56`	`:.:,:`	`[ -1.234.56]`
`[+++,++&.&&]`	`+1234.56`	`:.:,:`	`[ +1.234.56]`
`[$---,--&.&&]`	`-1234.56`	`E:.:,:`	`[E -1.234,56]`
`[$---,--&.&&]`	`+1234.56`	`E:.:,:`	`[E 1.234,56]`
`[$$$---,--&.&&]`	`+1234.56`	`E:.:,:`	`[E 1.234,56]`
`[$$$---,--&.&&]`	`+1234.56`	`USD:.:,:`	`[USD 1.234,56]`
`[---,--&.&&@]`	`-1234.56`	`:.:,:E`	`[ -1,234.56E]`
`[---,--&.&&@]`	`+1234.56`	`:.:,:E`	`[ 1,234.56E]`
`[---,--&.&&@@@]`	`+1234.56`	`:.:,:EUR`	`[ 1,234.56EUR]`
`[($---,--&.&&)]`	`-1234.56`	`E:.:,:`	`[(E -1,234.56)]`
`[($###,##&.&&)]`	`-1234.56`	`E:.:,:`	`[(E 1,234.56)]`
`[((((,(($.&&)]`	`-1234.56`	`E:.:,:`	`[ (E1,234.56)]`
`[((((,(($.&&)]`	`+1234.56`	`E:.:,:`	`[ E1,234.56 ]`
`[((((,(($.&&)]`	`-12.34`	`E:.:,:`	`[ (E12.34)]`
`[((((,(($.&&)]`	`+12.34`	`E:.:,:`	`[ E12.34 ]`
`[((((,(($.&&)]`	`0`	`E:.:,:`	`[ E.00 ]`

Formatting symbols for dates

The next table shows the formatting symbols for DATE expressions. (it does not apply to DATETIME values). Any other character is interpreted as a literal and will appear as is in the resulting string

Character	Description
`dd`	Day of the month as a 2-digit integer.
`ddd`	Three-letter English-language abbreviation of the day of the week. For example: Mon, Tue.
`mm`	Month as a 2-digit integer.
`mmm`	Three-letter English-language abbreviation of the month. For example: Jan, Feb.
`yy`	Year, as a 2-digits integer representing the 2 trailing digits.
`yyy`	Year as a 3-digit number (Ming Guo format only)
`yyyy`	Year as a 4-digit number.
`c1`	Ming Guo format modifier.

Table 3. Date formatting examples
Format String	Date value	Result string
`dd/mm/yyyy`	`2011-10-24`	`24/10/2011`
`[dd/mm/yy]`	`2011-10-24`	`[24/10/11]`
`[ddd-mmm-yyyy]`	`0141-10-24`	`[Tue-Oct-0141]`
`(ddd.) mmm. dd, yyyy`	`1999-09-23`	`(Thu.) Sep. 23, 1999`