Use when a SOQL SELECT clause or SOSL FIND/RETURNING clause must return number, date, time, or currency fields already localized to the running user's locale (e.g. $44,000.00 or 4/10/2025, 3:31 PM instead of a raw decimal or ISO 8601 string). Covers the FORMAT() function: its four supported field categories, the aliasing rule when the same field is queried twice, and nesting FORMAT() inside aggregate functions or convertCurrency(). NOT for date-literal WHERE filters (TODAY, LAST_N_DAYS), NOT for Apex-side String.format()/Datetime.format() rendering, and NOT for building the currency conversion itself (see convertCurrency in optimize-soql / data skills).
Use when a SOQL SELECT clause or SOSL FIND/RETURNING clause must return number, date, time, or currency fields already localized to the running user's locale (e.g. $44,000.00 or 4/10/2025, 3:31 PM instead of a raw decimal or ISO 8601 string). Covers the FORMAT() function: its four supported field categories, the aliasing rule when the same field is queried twice, and nesting FORMAT() inside aggregate functions or convertCurrency(). NOT for date-literal WHERE filters (TODAY, LAST_N_DAYS), NOT for Apex-side String.format()/Datetime.format() rendering, and NOT for building the currency conversion itself (see convertCurrency in optimize-soql / data skills).
["format a currency field in a SOQL query so it shows the locale currency symbol and thousands separators","return dates from a SOQL query already formatted for the user's locale instead of raw ISO 8601","get both the raw value and a localized display value of the same field in one query","make SOQL or SOSL output match what users see in the Salesforce UI for numbers and dates","apply FORMAT() to an aggregate MIN/MAX result or a convertCurrency() value in a query"]
inputs
["A SOQL SELECT (or SOSL FIND/RETURNING) query that returns number, date, time, or currency fields","Whether the caller needs the raw value, the localized display value, or both side by side","The execution context that runs the query (REST/SOAP/Bulk query API, dynamic Apex SOQL, or a report)"]
outputs
["A SOQL/SOSL query using FORMAT() with correct aliasing","Guidance on nesting FORMAT() inside aggregate or convertCurrency() functions","An explicit note that FORMAT() emits a locale-dependent String, not the typed field value"]
dependencies
[]
version
1.0.0
author
Pranav Nagrecha
updated
"2026-07-08T00:00:00.000Z"
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
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:
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:
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.
