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
| Situation | Recommended Approach | Reason |
|---|
| Consumer needs to do math or sort numerically | Return the raw field (no FORMAT()) | FORMAT() output is a string; arithmetic/sort on it is wrong |
| Display must match the Salesforce UI for the viewer | Wrap the field in FORMAT() with an alias | Localizes to the running user's locale, Classic-parity |
| Both machine value and display value needed | Select field twice: bare + FORMAT(field) alias | Aliasing is required when a field appears more than once |
Fixed-format integration contract (e.g. always yyyy-MM-dd) | Do not use FORMAT(); format explicitly downstream | Output varies by each user's locale — non-deterministic |
| Localizing an aggregate result | FORMAT(MIN(field)) alias | FORMAT() nests around aggregate functions |
| Localizing a converted currency amount | FORMAT(convertCurrency(field)) alias (multi-currency org) | FORMAT() nests around convertCurrency() |
| Filtering/sorting by the localized value | Filter/sort on the raw field instead | FORMAT() belongs in SELECT, not WHERE/ORDER BY |
Recommended Workflow
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
Salesforce-Specific Gotchas
Non-obvious platform behaviors that cause real production problems:
- 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.
- 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.
- 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
| Artifact | Description |
|---|
SOQL/SOSL query with FORMAT() | A query that returns localized, display-ready strings for the chosen number/date/time/currency columns, aliased correctly |
| Field/format worksheet | The templates/ cookbook filled in: each field mapped to raw / formatted / both, with the target locale and execution context recorded |
| Checker report | Output of scripts/check_soql_format_function_localization.py flagging missing aliases and misplaced FORMAT()/convertCurrency() usage |
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.
