SQL Server gives you two functions for formatting values: FORMAT() and CONVERT(). They overlap enough that it’s not always obvious which one to reach for. This article skips the feature-by-feature breakdown and focuses on answering the question of which one you should use for a given scenario.
What They Each Do
First, here’s a quick overview of each function:
CONVERT()has been in SQL Server for a long time. It converts a value from one data type to another, and when you’re working with dates, an optional style code lets you control the output format. It’s main purpose is to convert a value from one data type to another.FORMAT()was introduced in SQL Server 2012. It’s built around .NET format strings, which means you describe the output pattern yourself rather than picking from a list of pre-set codes. It works on dates, numbers, and currency. It’s main purpose is to provide formatting for a given value.
While their main purpose does differ, both functions can produce formatted strings from dates and numbers. The differences are in flexibility, performance, and how much control you actually need.
Use CONVERT() When…
You’re Formatting Dates in a Standard Format
CONVERT() covers the formats most people actually need. If you want DD/MM/YYYY, YYYY-MM-DD, or a datetime with the time included, there’s a style code for it. It’s faster to write than a FORMAT() string once you know the codes, and it performs significantly better on large datasets.
Here’s an example:
SELECT CONVERT(VARCHAR, GETDATE(), 103);Example output:
25/06/2026
You’re Querying a Large Table
This is the big one. FORMAT() runs through the .NET Common Language Runtime rather than SQL Server’s native engine. On a small result set you won’t notice the difference. On a query scanning millions of rows, FORMAT() can be dramatically slower, sometimes by an order of magnitude.
If your query touches a large table, use CONVERT(). The performance difference is real and it adds up fast.
You’re Converting Between Data Types Without Caring About Format
CONVERT() is the right tool any time you just need to change a value’s data type, but not style it. Converting a string to a date, an integer to a decimal, a date to a string without a specific format in mind is CONVERT() territory:
SELECT CONVERT(DATE, '2026-06-24')
SELECT CONVERT(DECIMAL(10,2), price)
SELECT CONVERT(VARCHAR, order_id)FORMAT() only converts to strings. It can’t go the other direction.
Use FORMAT() When…
You Need a Format CONVERT() Can’t Produce
CONVERT()‘s style codes cover a lot, but they’re a fixed list. If you need something outside that list, like June 24, 2026 with the full month name, or a date with an ordinal suffix, FORMAT() can handle it using .NET format strings:
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy');Result:
June 25, 2026
You’re Formatting Numbers or Currency
This is where FORMAT() really pulls ahead. CONVERT() has no equivalent for number formatting. If you want to display a number with thousands separators, a fixed number of decimal places, or as a currency value with a symbol, FORMAT() is your only built-in option:
SELECT
FORMAT(1234567.89, 'N2') AS N2,
FORMAT(1234567.89, 'C2') AS C2,
FORMAT(0.1534, 'P1') AS P1;Output:
N2 C2 P1
-------------- ----------------- -----------
1,234,567.89 $1,234,567.89 15.3%
The N, C, and P specifiers stand for Number, Currency, and Percent. The number after them controls decimal places.
You Need Locale-Aware Formatting
FORMAT() accepts an optional culture parameter, which lets you format values according to a specific locale’s conventions. That’s useful when your application serves multiple regions and the formatting needs to match.
SELECT
FORMAT(1234567.89, 'C2', 'en-GB') AS "en-GB",
FORMAT(1234567.89, 'C2', 'de-DE') AS "de-DE",
FORMAT(GETDATE(), 'D', 'fr-FR') AS "fr-FR";Output:
en-GB de-DE fr-FR
-------------- --------------- ------------------
£1,234,567.89 1.234.567,89 € jeudi 25 juin 2026
CONVERT() has no equivalent for this. If locale matters, FORMAT() is the answer.
You’re Working With a Small Dataset
If you’re pulling a handful of rows (such as a report summary, a single record lookup, a dashboard widget) the performance gap between FORMAT() and CONVERT() will be negligible. In those cases, use whichever produces the output you need most cleanly or whichever you feel most comfortable with.
Quick Decision Guide
| Scenario | Use |
|---|---|
| Formatting a date in a standard format (DD/MM/YYYY, YYYY-MM-DD, etc.) | CONVERT() |
| Querying a large table | CONVERT() |
| Converting between data types | CONVERT() |
| Converting a string to a date | CONVERT() |
| Formatting a date with a full month name or custom pattern | FORMAT() |
| Formatting numbers with thousands separators | FORMAT() |
| Displaying currency with a symbol | FORMAT() |
| Formatting percentages | FORMAT() |
| Locale-aware formatting for different regions | FORMAT() |
| Small dataset, any format | Either |
What About CAST()?
You’ll often see CAST() mentioned alongside these two. It’s simpler than CONVERT() in that it converts between data types but offers no formatting control at all. No style codes, no format strings. It’s a good choice when you just need a type conversion and the output format doesn’t matter.
The SQL standard actually prefers CAST() over CONVERT() for pure type conversions, since CONVERT() with its style codes is SQL Server-specific. If you’re writing code that might need to run on other database systems, CAST() is more portable. But for conversions that require formatting, CONVERT() and FORMAT() are your tools.
The Short Version
If you’re formatting dates in a common format and performance matters, use CONVERT(). If you need number or currency formatting, locale support, or a date format that CONVERT() can’t produce, use FORMAT().