Soql Format Function Localization

SOQL FORMAT() Function — Localized Output

This skill activates when a query must return display-ready, locale-aware strings for number, date, time, or currency fields — so exported, integrated, or reported values match what a user sees in the Salesforce UI, rather than the raw decimal or ISO 8601 value the query engine returns by default. The mechanism is the FORMAT() function in a SOQL SELECT clause or a SOSL RETURNING field list.

Before Starting

Gather this context before working on anything in this domain:

• Confirm the field is one of the four supported categories. FORMAT() applies "localized formatting to standard and custom number, date, time, and currency fields." It does nothing for text, picklist, reference, or boolean fields — wrapping those is a mistake, not a no-op you can ignore.
• Decide whether you need the raw value too. FORMAT() returns a string, not the underlying typed value. If a consumer needs to do math, sort numerically, or round-trip the value, keep the unformatted field in the query as well — which then forces the aliasing rule below.
• Know whose locale drives the output. Formatting reflects "the appropriate format for the given user locale" — the running user's Locale, not the org default. The same query returns 12/28/2015 for a US user and 28/12/2015 or 28.12.2015 for others. That non-determinism is fine for UI parity and wrong for a fixed-format integration contract.
• No maturity gate applies, but do not invent one. The SOQL and SOSL reference pages document FORMAT() as a standard SELECT-clause function with no Beta, Pilot, or Developer Preview label. Treat it as standard, generally documented SOQL/SOSL functionality; do not assert a GA/Beta status the docs don't state.

Core Concepts

What FORMAT() does

FORMAT() wraps a field in the SELECT list and returns a locale-formatted string representation of that field's value. Per the reference, "when the FORMAT function is applied, fields reflect the appropriate format for the given user locale," and the result "matches what appears in the Salesforce Classic user interface." Concretely:

• Datetime — without FORMAT(), LastModifiedDate returns 2025-04-10T22:31:37.000+0000; with it, 4/10/2025, 3:31 PM.
• Currency — "using FORMAT with currencies returns a fully formatted value, such as $44,000.00, instead of a plain number."
• Date — December 28, 2015 can come back as 2015-12-28, 28-12-2015, 28/12/2015, 12/28/2015, or 28.12.2015 depending on the org/user locale.

The output is a display artifact. It is not the field's typed value, and no downstream consumer should parse it as a number or a canonical date.

The four supported categories — and only four

FORMAT() "supports standard and custom number, date, time, and currency fields." That is the complete list: number, date, time, currency. There is no localized formatting of text, ID, picklist, or checkbox fields through this function.

Aliasing: optional, until it isn't

"The FORMAT function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times." The canonical shape is the raw field and its formatted twin side by side, and the formatted one must carry an alias:

SELECT Id, LastModifiedDate, FORMAT(LastModifiedDate) formattedDate FROM Account

Without the formattedDate alias, the query references LastModifiedDate twice and errors. Give the formatted column a stable alias whenever the raw field is also selected.

Nesting with aggregate and convertCurrency()

FORMAT() can wrap two other function results: "you can also nest it with aggregate or convertCurrency() functions."

• Aggregate — SELECT FORMAT(MIN(closedate)) Amt FROM opportunity. The aggregate runs, then FORMAT() localizes the single scalar it produces. Aggregate queries return AggregateResult, so you read the value by its alias.
• convertCurrency() — SELECT amount, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity. convertCurrency() requires a multi-currency org and "can't [be used] in a WHERE clause." It converts to the user's currency; FORMAT() then renders that converted amount with the locale symbol and separators.

SOSL is symmetric

The same function exists for SOSL, applied inside a RETURNING field list: "use FORMAT with the FIND clause to apply localized formatting." Example: FIND {Acme} RETURNING Account(Id, LastModifiedDate, FORMAT(LastModifiedDate) FormattedDate). The aliasing and nesting rules are identical.

Common Patterns

Raw + formatted side by side

When to use: a consumer needs the machine value for logic and a localized string for display in the same result row.

How it works: select the field twice — once bare, once wrapped — and alias the formatted column: SELECT Id, Amount, FORMAT(Amount) amountDisplay FROM Opportunity. The alias is mandatory here because Amount appears twice.

Why not the alternative: returning only the formatted value strands any code that needs to compute or compare; returning only the raw value pushes locale formatting into every client, duplicating logic Salesforce already localizes correctly.

UI-parity reporting / export

When to use: an export or emailed report must read exactly like the Salesforce UI (currency symbols, thousands separators, locale dates) without a client-side formatting layer.

How it works: wrap each number/date/time/currency column in FORMAT() with a clear alias. The API query then returns display-ready strings that match Classic's rendering for the running user's locale.

Why not the alternative: hand-formatting on the client re-implements locale rules per platform and drifts from the org's actual settings; FORMAT() centralizes it in the query.

Localized aggregate scalar

When to use: a dashboard tile or summary needs one localized figure — earliest close date, largest deal.

How it works: nest the aggregate inside FORMAT() and alias it — SELECT FORMAT(MIN(CloseDate)) earliest FROM Opportunity. Read earliest off the AggregateResult.

Decision Guidance

Recommended Workflow

1. Classify the fields. Confirm each field you want to localize is a number, date, time, or currency field — the only four categories FORMAT() supports. Drop the wrapper from any other type.
2. Decide raw vs formatted vs both. If any consumer needs the typed value, keep the bare field in the SELECT in addition to the formatted one.
3. Write the query and alias correctly. Wrap the display columns in FORMAT(). If the same field appears twice (raw + formatted), give the formatted column a required alias; alias aggregate/converted columns too.
4. Add nesting where needed. For a summary figure use FORMAT(<aggregate>); for a converted amount in a multi-currency org use FORMAT(convertCurrency(<field>)). Keep convertCurrency() out of any WHERE clause.
5. Retrieve by alias. When the query runs as an aggregate (or in dynamic Apex SOQL), read each formatted column by its alias off the AggregateResult/sObject, and type it as a String.
6. Verify against locale and context. Confirm the rendered strings match the target locale, that no integration contract depends on a fixed format, and — if the query is inline Apex — that it compiles in your API version (prefer dynamic Database.query if inline support is uncertain). Run scripts/check_soql_format_function_localization.py over the source.

Review Checklist

Run through these before marking work in this area complete:

• Every FORMAT() wraps a number, date, time, or currency field — nothing else
• Any field selected both raw and formatted has an alias on the formatted column
• Aggregate and convertCurrency() columns wrapped by FORMAT() are aliased
• No FORMAT() (or convertCurrency()) sits in a WHERE, HAVING, or ORDER BY clause
• Consumers that need to compute/sort receive the raw field, not the formatted string
• No integration contract silently depends on a per-user, locale-variable string
• No GA/Beta/Pilot maturity claim was made about FORMAT() beyond what the docs state

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. The output is a String, not the value — FORMAT(Amount) returns "$44,000.00", so any downstream arithmetic, numeric sort, or Date parsing breaks. Keep the raw field for logic and use the formatted column only for display.
2. Locale is the running user's, not the org's — the same query returns 12/28/2015 for one user and 28.12.2015 for another. Great for UI parity, silently corrupting for a fixed-format export or a partner integration that expects one canonical format.
3. Duplicate-field aliasing is mandatory, not optional — selecting LastModifiedDate and FORMAT(LastModifiedDate) without an alias errors, because the field now appears twice. The alias is what disambiguates the two columns.

Output Artifacts

Related Skills

• apex/soql-fundamentals — the base SELECT syntax, aliasing, and function placement this skill builds on; consult it for overall query structure, then apply FORMAT() for the localized output layer.
• data/multi-currency-and-advanced-currency-management — the multi-currency (and dated-exchange-rate) setup that convertCurrency() depends on before you can wrap it in FORMAT().
• apex/soql-security — CRUD/FLS still gates the underlying field even when the output is a formatted string; use it to keep FORMAT() queries enforcing field access.