FORMAT SQL Scalar Function

Description

In BBj 26.03 and higher formats a numeric, string, or date/time/timestamp value as a string, using either a named preset style or a custom formatting pattern, optionally localized to a specific locale.

Syntax

FORMAT ( value, format_type [ , locale ] )

Return Type

VARCHAR

Arguments

Argument Type Description
value NUMERIC, CHARACTER, or DATE/TIME/TIMESTAMP The value to format. If value is NULL, FORMAT returns NULL.
format_type CHARACTER A case-insensitive preset keyword (see below) or a custom formatting pattern. This argument is required; if it is NULL, FORMAT raises an error.
locale CHARACTER (optional) An IETF BCP 47 language tag (for example, 'en-US' or 'de-DE') used to localize the output. If this argument is omitted, NULL, or blank, the system default locale is used.

Preset Keywords

When format_type matches one of the keywords below (case-insensitive), FORMAT applies the corresponding built-in style. Each preset only applies to values of a compatible type; applying a preset to an incompatible value type (for example, a currency preset applied to a date) raises an error.

Numeric Presets

Keyword Description Example (en-US) Example (de-DE)
number, decimal Standard grouping separators with up to 2 decimal places. 1234567.89 → "1,234,567.89" 1234567.89 → "1.234.567,89"
currency Localized currency symbol, grouping, and 2 decimal places. 1234.50 → "$1,234.50" 1234.50 → "1.234,50 €"
percent Multiplies the input by 100 and appends the local percent sign. 0.854 → "85.4%" 0.854 → "85,4 %"
compact Abbreviated, human-readable notation. 1500000 → "1.5M" 1500000 → "1,5 Mio."
phone Formats a 10- or 11-digit number into a local phone format. 18005550199 → "+1 (800) 555-0199" 18005550199 → "+1 800 555 0199"

Date / Time / Timestamp Presets

Keyword Description Example (en-US) Example (de-DE)
date Medium localized date. 2026-09-18 → "Sep 18, 2026" 2026-09-18 → "18.09.2026"
short_date Concise numeric date. 2026-09-18 → "9/18/26" 2026-09-18 → "18.09.26"
long_date Full written date. 2026-09-18 → "September 18, 2026" 2026-09-18 → "18. September 2026"
time Medium localized time. 14:30:00 → "2:30:00 PM" 14:30:00 → "14:30:00"
datetime Combined localized date and time. Requires a full TIMESTAMP value. 2026-09-18 14:30:00 → "Sep 18, 2026, 2:30:00 PM" __

The date, short_date, and long_date presets cannot be applied to a TIME-only value. The time preset cannot be applied to a DATE-only value. The datetime preset requires a TIMESTAMP value.

String Presets

Keyword Description Example
uppercase Converts the string to uppercase using the specified locale. "hello" → "HELLO"
lowercase Converts the string to lowercase using the specified locale. "HELLO" → "hello"
capitalize Capitalizes the first letter of each word. "john doe" → "John Doe"

Custom Patterns

If format_type does not match a recognized keyword, it is treated as a custom formatting pattern based on the type of value:

  • Numeric values — the pattern is applied using a DecimalFormat paired with the decimal symbols for the specified locale. Example: FORMAT(1234.5, '#,##0.00').

  • Date/time/timestamp values — the pattern is applied using a localized DateTimeFormatter pattern. Example: FORMAT(order_date, 'yyyy-MM-dd').

  • String values — the pattern is treated as a character mask. Each # character in the pattern is replaced, in order, with the next character from the input string; all other characters in the pattern are copied through literally. Example: FORMAT('123456789', '###-##-####') returns "123-45-6789".

Examples

FORMAT(1234567.89, 'number', 'en-US')    --> '1,234,567.89'
FORMAT(1234.50, 'currency', 'de-DE')     --> '1.234,50 €'
FORMAT(0.854, 'percent')                 --> '85.4%'   (default locale)
FORMAT(1500000, 'compact', 'en-US')      --> '1.5M'
FORMAT(18005550199, 'phone', 'en-US')    --> '+1 (800) 555-0199'
FORMAT(hire_date, 'long_date', 'de-DE')  --> '18. September 2026'
FORMAT(start_time, 'time', 'en-US')      --> '2:30:00 PM'
FORMAT('john doe', 'capitalize')         --> 'John Doe'
FORMAT(unit_price, '#,##0.00')           --> '1,234.50'
FORMAT(order_date, 'yyyy-MM-dd')         --> '2026-09-18'
FORMAT(ssn, '###-##-####')               --> '123-45-6789'

Error Conditions

  • format_type is NULL.

  • A preset keyword is applied to an incompatible value type (for example, currency applied to a date, or date applied to a string).

  • A date/time preset is applied to a DATETIME value that is missing the required date or time component (for example, time applied to a DATE-only value, or datetime applied to a value that is not a full TIMESTAMP).

  • phone is applied to a number that does not have 10 or 11 digits.

  • A custom pattern is not valid for the type of value being formatted.

Notes

  • All keyword matching for format_type is case-insensitive.

  • If locale is omitted, NULL, or blank, formatting falls back to the system default locale.

  • FORMAT is deterministic (memoizable) for a given set of arguments.