Skip to main content

FORMAT() SQL FUNCTION

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

Associate Data Engineer in SQL

Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
Explore Track

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

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.

Get your Certification
Timeline mobile.png
Topics
SQL
Data Analysis

Popular SQL Courses

Course

Introduction to SQL

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

Tutorial

DATEDIFF() SQL FUNCTION

DATEDIFF() is one of the most widely used date data manipulation functions in SQL. Master it by reading this tutorial.
Travis Tang 's photo

Travis Tang

3 min

Tutorial

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

Tutorial

How to Use the COALESCE() Function in SQL (With Examples)

Learn how to use the SQL COALESCE() function to handle null values, combine columns, and clean up your data with real-world examples and tips.
Travis Tang 's photo

Travis Tang

5 min

Tutorial

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

Tutorial

How to Use the SQL REPLACE() Function

Learn the application of the SQL REPLACE() function in text manipulation. Understand the use of the REPLACE() function in data cleaning and database management.
Allan Ouko's photo

Allan Ouko

10 min

Tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

See MoreSee More