SQL FORMAT() Function: Syntax, Usage, and Examples

The SQL FORMAT function allows you to convert numeric or date/time values into a formatted string using a specific pattern or culture. This function is especially useful in reports, dashboards, or any query where you want clean, user-friendly formatting of raw data. FORMAT is commonly supported in SQL Server and can simplify many presentation-level formatting tasks.

How to Use FORMAT in SQL

The basic syntax of SQL FORMAT is:

FORMAT(value, format_string [, culture])

• value: A numeric or date/time expression.
• format_string: A string that defines how the output should appear.
• culture (optional): A locale code like 'en-US', 'de-DE', or 'fr-FR' that determines regional formatting.

Basic Example

SELECT FORMAT(12345.678, 'N2') AS formatted_number;
-- Output: 12,345.68

This formats the number with two decimal places and commas as thousand separators.

When to Use FORMAT

Use FORMAT whenever you want to enhance the readability of your numeric or date outputs. Common scenarios include:

• Displaying numbers with thousands separators and fixed decimals
• Formatting currency values based on locale
• Rendering dates in human-readable or regional formats
• Outputting results for reports or front-end interfaces
• Customizing logs, exports, or receipts

Report-Friendly Numbers

SELECT FORMAT(total_sales, 'N0') AS sales_display
FROM sales_summary;

Shows sales with commas and no decimal places.

Regional Currency Formatting

SELECT FORMAT(price, 'C', 'en-US') AS price_usd,
       FORMAT(price, 'C', 'de-DE') AS price_eur
FROM products;

This function displays prices in US dollars and euros with correct currency symbols and formatting.

Examples of FORMAT

Example 1: Formatting Currency

SELECT FORMAT(1999.99, 'C', 'en-US') AS formatted_price;
-- Output: $1,999.99

This formats the number as US currency.

Example 2: Formatting Dates

SELECT FORMAT(order_date, 'MMMM dd, yyyy') AS readable_date
FROM orders;

Returns a date like April 05, 2023.

Example 3: Leading Zeros and Custom Number Patterns

SELECT FORMAT(42, '00000') AS padded_value;
-- Output: 00042

This FORMAT function pads the number to five digits.

Example 4: Time Formatting

SELECT FORMAT(order_time, 'hh:mm tt') AS formatted_time
FROM orders;
-- Output: 03:15 PM

Formats time in 12-hour format with AM/PM.

Example 5: Combining FORMAT with Other SQL Functions

SELECT FORMAT(SUM(amount), 'C', 'en-GB') AS total_sales
FROM transactions
WHERE transaction_date >= '2023-01-01';

Useful in aggregated or grouped queries for reporting.

Learn More About SQL FORMAT

Date Format Patterns

The FORMAT function supports many custom date patterns:

• 'yyyy-MM-dd' → 2023-06-01
• 'dd/MM/yyyy' → 01/06/2023
• 'MMM dd, yyyy' → Jun 01, 2023
• 'dddd, MMMM dd' → Thursday, June 01

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd') AS full_day;

Displays the current date with full day and month names.

Numeric Format Patterns

Some common numeric formats include:

• 'N' – Number with decimals (e.g., N2 = 2 decimal places)
• 'C' – Currency
• 'P' – Percentage
• 'E' – Scientific notation
• 'G' – General (default, culture-specific)

SELECT FORMAT(0.867, 'P2') AS percent_value;
-- Output: 86.70 %

This formats a decimal as a percentage with two decimal places.

Culture Settings

Culture codes control the output’s regional style, including:

• 'en-US' → English (United States)
• 'en-GB' → English (United Kingdom)
• 'de-DE' → German (Germany)
• 'fr-FR' → French (France)
• 'ja-JP' → Japanese

SELECT FORMAT(GETDATE(), 'D', 'ja-JP') AS japanese_date;

This returns the full Japanese-formatted date.

FORMAT vs CONVERT and CAST

While CAST() and CONVERT() change data types, FORMAT() changes presentation.

-- CAST example (no formatting control)
SELECT CAST(order_date AS VARCHAR) FROM orders;

-- FORMAT example (formatted output)
SELECT FORMAT(order_date, 'dd-MM-yyyy') FROM orders;

Use FORMAT when you care about how the result looks, not just its type.

Performance Considerations

FORMAT is powerful but not always fast. Since it returns NVARCHAR, and must parse both value and format, it’s slower than simpler casting. Use it for:

• Final output
• UI queries
• Read-only dashboards

Avoid using FORMAT in WHERE clauses or heavily nested logic.

Common Pitfalls

• Can't use for indexing: Since it returns a string, don't filter or join on it.
• Returns strings: You can’t perform math on formatted results.
• Can break localization: Hardcoding date formats can confuse users in other regions.

FORMAT Inside Aggregates and CTEs

You can use FORMAT inside SELECT, CTE, or subqueries:

WITH monthly_sales AS (
  SELECT region, FORMAT(SUM(sales), 'C', 'en-US') AS formatted_sales
  FROM sales
  GROUP BY region
)
SELECT * FROM monthly_sales;

This prepares readable totals per region.

Formatting in JSON Exports

When building JSON with SQL Server’s FOR JSON clause, FORMAT makes the output cleaner:

SELECT
  name,
  FORMAT(price, 'C') AS price,
  FORMAT(order_date, 'yyyy-MM-dd') AS order_date
FROM products
FOR JSON AUTO;

This is helpful when sending formatted data to client-side apps.

The FORMAT function is ideal for beautifying the results of your queries. From formatting prices and percentages to customizing dates for specific locales, it gives you precise control over how data appears. While not meant for heavy data processing, FORMAT is a powerful tool for polishing reports, improving readability, and delivering well-structured outputs across various systems and users.