Skip to main content
HomeTutorialsSQL

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Updated 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

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

FORMAT(@d, 'f', 'en-gb')

01 February 2023 00:00

German (de-de)

FORMAT(@d, 'd', 'de-de') 

01.02.2023

FORMAT(@d, 'f', 'de-de') 

Mittwoch, 1. Februar 2023 00:00

Chinese (zh-cn)

FORMAT(@d, 'd', 'zh-cn')

2023/2/1

FORMAT(@d, 'f', 'zh-cn')

2023年2月1日 0:00

Indian (hi-in)

FORMAT(@d, 'd', ‘hi-in')

01-02-0203

FORMAT(@d, 'f', ‘hi-in')

01 फरवरी 2023 00:00

Russian (ru-ru)

FORMAT(@d, 'd', ru-ru') 

01-02-2023

FORMAT(@d, 'f', ru-ru') 

1 февраля 2023 г. 0:00

Spain (gl-es)

FORMAT(@d, 'd', 'gl-es')

01/02/2023

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.

See also

Learn more about SQL

Topics
SQL
Data Analysis

Popular SQL Courses

Course

Introduction to SQL

2 hr
630.4K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

SQL Developer Salaries: Expectations in 2024

In this article, we're going to learn what an SQL developer does, what factors impact SQL developer salaries, how the average SQL developer salary compares to the average salaries of other developer profiles, and how you can increase your salary as an SQL developer.
Elena Kosourova's photo

Elena Kosourova

7 min

Scaling Enterprise Analytics with Libby Duane Adams, Chief Advocacy Officer and Co-Founder of Alteryx

RIchie and Libby explore the differences between analytics and business intelligence, generative AI and its implications in analytics, the role of data quality and governance, Alteryx’s AI platform, data skills as a workplace necessity, and more. 
Richie Cotton's photo

Richie Cotton

43 min

[Radar Recap] Building a Learning Culture for Analytics Functions, with Russell Johnson, Denisse Groenendaal-Lopez and Mark Stern

In the session, Russell Johnson, Chief Data Scientist at Marks & Spencer, Denisse Groenendaal-Lopez, Learning & Development Business Partner at Booking Group, and Mark Stern, VP of Business Intelligence & Analytics at BetMGM will address the importance of fostering a learning environment for driving success with analytics.
Adel Nehme's photo

Adel Nehme

41 min

[Radar Recap] From Data Governance to Data Discoverability: Building Trust in Data Within Your Organization with Esther Munyi, Amy Grace, Stefaan Verhulst and Malarvizhi Veerappan

Esther Munyi, Amy Grace, Stefaan Verhulst and Malarvizhi Veerappan focus on strategies for improving data quality, fostering a culture of trust around data, and balancing robust governance with the need for accessible, high-quality data.
Richie Cotton's photo

Richie Cotton

39 min

[Radar Recap] Scaling Data ROI: Driving Analytics Adoption Within Your Organization with Laura Gent Felker, Omar Khawaja and Tiffany Perkins-Munn

Laura, Omar and Tiffany explore best practices when it comes to scaling analytics adoption within the wider organization
Richie Cotton's photo

Richie Cotton

40 min

50 Years of SQL with Don Chamberlin, Computer Scientist and Co-Inventor of SQL

Richie and Don explore the early development of SQL, the commercialization and adoption of SQL, how it became standardized, how it evolved and spread via open source, the future of SQL through NoSQL and SQL++ and much more.
Richie Cotton's photo

Richie Cotton

36 min

See MoreSee More