FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.

Oct 2022 · 3 min read

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?
`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

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	`FORMAT(@d, ‘d')`	2/1/2023
D	`FORMAT(@d, D)`	Wednesday, February 1, 2023
f	`FORMAT(@d, ‘f')`	Wednesday, February 1, 2023 4:05 AM
F	`FORMAT(@d, ‘F')`	Wednesday, February 1, 2023 4:05:06 AM
g	`FORMAT(@d, ‘g')`	10/8/2022 6:01 AM
G	`FORMAT(@d, ‘G')`	10/8/2022 6:01:06 AM
O	`FORMAT(@d, ‘O')`	2022-10-08T06:01:06.117Z
r	`FORMAT(@d, ‘r')`	Wed, 01 Feb 2023 04:05:06 GMT
R	`FORMAT(@d, ‘R')`	Wed, 01 Feb 2023 04:05:06 GMT
s	`FORMAT(@d, ‘s')`	2023-02-01T04:05:06
u	`FORMAT(@d, ‘u')`	2023-02-01 04:05:06Z
U	`FORMAT(@d, ‘U')`	Wednesday, February 1, 2023 4:05:06 AM
t	`FORMAT(@d, ‘t')`	4:05 AM
T	`FORMAT(@d, ‘T')`	4:05:06 AM
Y	`FORMAT(@d, ‘Y')`	February 2023
MM_dd_yyyy	`FORMAT(@d, ‘MM_dd_yyyy')`	02_01_2023
MMM-dd-yy	`FORMAT(@d,'MMM-dd-yy')`	Feb 01 23
yyyy-dd-MM	`FORMAT(@d,'yyyy-dd-MM')`	2023-01-02
yyyy-dd-MM hh.mm	`FORMAT(@d,'yyyy-dd-MM hh.mm')`	2023-01-02 04.05
yyyy-dd-MM hh.mm.ss	`FORMAT(@d,'yyyy-dd-MM hh.mm.ss')`	2023-01-02 04.05.06
yyyy-dd-MM hh.mm.ss tt	`FORMAT(@d,'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)	`FORMAT(@d, 'd', 'en-US')`	2/1/2023
US English (en-US)	`FORMAT(@d, 'f', 'en-US')`	Wednesday, February 1, 2023 12:00 AM
Great Britain English (en-gb)	`FORMAT(@d, 'd', 'en-gb')`	01/02/2023
Great Britain English (en-gb)	`FORMAT(@d, 'f', 'en-gb')`	01 February 2023 00:00
German (de-de)	`FORMAT(@d, 'd', 'de-de')`	01.02.2023
German (de-de)	`FORMAT(@d, 'f', 'de-de')`	Mittwoch, 1. Februar 2023 00:00
Chinese (zh-cn)	`FORMAT(@d, 'd', 'zh-cn')`	2023/2/1
Chinese (zh-cn)	`FORMAT(@d, 'f', 'zh-cn')`	2023年2月1日 0:00
Indian (hi-in)	`FORMAT(@d, 'd', ‘hi-in')`	01-02-0203
Indian (hi-in)	`FORMAT(@d, 'f', ‘hi-in')`	01 फरवरी 2023 00:00
Russian (ru-ru)	`FORMAT(@d, 'd', ru-ru')`	01-02-2023
Russian (ru-ru)	`FORMAT(@d, 'f', ru-ru')`	1 февраля 2023 г. 0:00
Spain (gl-es)	`FORMAT(@d, 'd', 'gl-es')`	01/02/2023
Spain (gl-es)	`FORMAT(@d, 'f', 'gl-es')`	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.

Learn more about SQL

Topics

SQL

Data Analysis

Popular SQL Courses

course

Introduction to SQL

2 hours

661.7K

Learn how to create and query relational databases using SQL in just two hours.

See Details

Start Course

course

Intermediate SQL

4 hours

210.1K

Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!

See Details

Start Course

course