Course
Data Manipulation in SQL
4 hr
323K
Database output is rarely report-ready. Prices come back as 1234.5 instead of $1,234.50. Dates display as 2023-02-01 04:05:06 when you wanted something more readable.
SQL's FORMAT() function can be useful. It works by converting numbers and dates into formatted text and is has decent support for different currencies or custom patterns of some kind.
This tutorial walks through the syntax and four common use cases.
What Is the FORMAT() Function?
FORMAT() turns numbers or datetimes into text, with rules for how they are displayed.
When to Use FORMAT() in SQL
FORMAT() comes in handy for displaying dates, currency, and numeric values in a specific format.
SQL FORMAT() Syntax
FORMAT(value, format[, culture])|
Parameter |
What is it? |
|
Value |
A required parameter referring to the column to be formatted |
|
Format |
A required parameter specifying the format pattern of the value. It should contain a value .NET format string. We will illustrate the various allowed formats in the examples below |
|
Culture |
An optional parameter specifying the locale-aware formatting of date/time. |
FORMAT() Examples
Example 1: Formatting numeric variables
SELECT
FORMAT(0112223333, ‘##-###-####') -- replace format with what is shown in the table below.|
phone_number |
|
|
0 |
11-222-3333 |
Associate Data Engineer in SQL
Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
Example 2: Formatting datetime
We can format a variable of datetime format in different formats.
DECLARE @d DATETIME = CAST('2023-02-01 04:05:06' AS DATETIME);
SELECT
FORMAT(@d, format) -- replace format with what is shown in the table below.|
Format |
Query |
Example of formatted date for 1 February 2023 04:05:06 AM |
|
d |
|
2/1/2023 |
|
D |
|
Wednesday, February 1, 2023 |
|
f |
|
Wednesday, February 1, 2023 4:05 AM |
|
F |
|
Wednesday, February 1, 2023 4:05:06 AM |
|
g |
|
10/8/2022 6:01 AM |
|
G |
|
10/8/2022 6:01:06 AM |
|
O |
|
2022-10-08T06:01:06.117Z |
|
r |
|
Wed, 01 Feb 2023 04:05:06 GMT |
|
R |
|
Wed, 01 Feb 2023 04:05:06 GMT |
|
s |
|
2023-02-01T04:05:06 |
|
u |
|
2023-02-01 04:05:06Z |
|
U |
|
Wednesday, February 1, 2023 4:05:06 AM |
|
t |
|
4:05 AM |
|
T |
|
4:05:06 AM |
|
Y |
|
February 2023 |
|
MM_dd_yyyy |
|
02_01_2023 |
|
MMM-dd-yy |
|
Feb 01 23 |
|
yyyy-dd-MM |
|
2023-01-02 |
|
yyyy-dd-MM hh.mm |
|
2023-01-02 04.05 |
|
yyyy-dd-MM hh.mm.ss |
|
2023-01-02 04.05.06 |
|
yyyy-dd-MM hh.mm.ss tt |
|
2023-01-02 04.05.06 AM |
Example 3: Formatting culture-aware date
We can format dates into different languages with the culture parameter.
DECLARE @d DATE = CAST('2023-02-01' AS DATE);
SELECT
FORMAT(@d, format, culture) -- replace format with what is shown in the table below.|
Culture |
Query |
Example of formatted date for 1 February 2023 |
|
US English (en-US) |
|
2/1/2023 |
|
|
Wednesday, February 1, 2023 12:00 AM |
|
|
Great Britain English (en-gb) |
|
01/02/2023 |
|
|
01 February 2023 00:00 |
|
|
German (de-de) |
|
01.02.2023 |
|
|
Mittwoch, 1. Februar 2023 00:00 |
|
|
Chinese (zh-cn) |
|
2023/2/1 |
|
|
2023年2月1日 0:00 |
|
|
Indian (hi-in) |
|
01-02-0203 |
|
|
01 फरवरी 2023 00:00 |
|
|
Russian (ru-ru) |
|
01-02-2023 |
|
|
1 февраля 2023 г. 0:00 |
|
|
Spain (gl-es) |
|
01/02/2023 |
|
|
mércores 01 febreiro 2023 00:00 |
Example 4: Formatting currency
We can also conveniently format numeric values into currencies.
SELECT
FORMAT(amount, 'c', culture) -- replace format with what is shown in the table below.|
Culture |
Query |
Example of formatted currency |
|
US English (en-US) |
SELECT FORMAT(5.5, 'c', 'en-US') |
$5.5 |
|
Great Britain English (en-gb) |
SELECT FORMAT(@d, 'c', 'en-gb') |
£5.5 |
|
German (de-de) |
SELECT FORMAT(@d, 'c', 'de-de') |
5,50 € |
|
Chinese (zh-cn) |
SELECT FORMAT(@d, 'c', 'zh-cn') |
¥5.50 |
|
Indian (hi-in) |
SELECT FORMAT(@d, 'c', ‘hi-in') |
₹5.50 |
|
Russian (ru-ru) |
SELECT FORMAT(@d, 'c', ru-ru') |
5,50 ₽ |
|
Spain (gl-es) |
SELECT FORMAT(@d, 'c', 'gl-es') |
€5,50 |
Technical Requirements
FORMAT() works in SQL Server (starting with 2012), Azure SQL Database. FORMAT() also works in PostgreSQL, but behaves differently. You can learn more about it in this course.
See Also
Learn More About SQL
Conclusion
FORMAT() is worth learning because it transforms one of SQL's annoyances into a one-line fix.
One caveat worth keeping in mind: this is SQL Server and Azure SQL syntax. PostgreSQL has its own FORMAT() function, but it behaves differently — so check your dialect before relying on these patterns.
Get certified in your dream Data Engineer role
Our certification programs help you stand out and prove your skills are job-ready to potential employers.
Topics
