Course
Data Manipulation in SQL
4 hr
296.9K
What is the FORMAT() function?
FORMAT() turns numbers or datetimes into text, with rules for how they are displayed.
When to use FORMAT()
FORMAT() comes in handy for displaying dates, currency, and numeric values in a specific format.
FORMAT() syntax
FORMAT(value, format[, culture])|
Parameter |
What is it? |
|
|
A required parameter referring to the column to be formatted |
|
|
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 |
|
|
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.|
|
|
|
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) |
|
$5.5 |
|
Great Britain English (en-gb) |
|
£5.5 |
|
German (de-de) |
|
5,50 € |
|
Chinese (zh-cn) |
|
¥5.50 |
|
Indian (hi-in) |
|
₹5.50 |
|
Russian (ru-ru) |
|
5,50 ₽ |
|
Spain (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
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
