Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

SQL FORMAT Function

FORMAT returns a value in a specified format and optional culture.

FORMAT converts dates, times, datetimes, currency, and number data types.

This function offers flexible format specifiers, both general and customized.

Example

#

This example formats a number as currency.

SELECT FORMAT(70.95,'c') AS Price
Result: 1 record
Price
$70.95

Using FORMAT

#

FORMAT supports locale-aware formatting of date/time and number values as strings.

Alternatively, use CAST and CONVERT for other data type conversions.

Syntax

Syntax for the FORMAT function.

  1. FORMAT (value, format [,culture])

value -- a value or column name which will be formatted.

format -- accepts a valid format pattern.

culture -- an optional parameter to set the culture.

See below for value, format, and culture examples.


Formatting dates

Return the current date as 'month day'.

SELECT FORMAT( GETDATE(), 'M' ) AS Today
Result:  1 record
Today
December 21

Dates and times can be formatted with standard and custom format specifiers.

Standard Format Specifiers

#

The standard date and time format specifiers for FORMAT.

Format Description Query Result
d Displays month, day, and year
Format: month/date/year
SELECT FORMAT (GETDATE(), 'd') 8/24/2021
D Displays weekday, month name, date, and year
Format: weekday, monthname date, year
SELECT FORMAT (GETDATE(), 'D') Tuesday, August 24, 2021
f Displays timestamp together with the output of D parameter.
This format does not include seconds.
Format: weekday, monthname date, year hour:minutes AM / PM
SELECT FORMAT (GETDATE(), 'f') Tuesday, August 24, 2021 12:05 PM
F Adds seconds information to the f parameter.
Format: weekday, monthname day, year hour:minutes:seconds AM / PM
SELECT FORMAT (GETDATE(), 'F') Tuesday, August 24, 2021 12:36:02 PM
g Format: month/date/year hour:minute AM / PM SELECT FORMAT (GETDATE(), 'g') 8/24/2021 12:39 PM
G Displays the same result as g format with seconds information.
Format: month/date/year hour:minute:seconds AM / PM
SELECT FORMAT (GETDATE(), 'G') 8/24/2021 12:50:03 PM
M Displays the month name and day.
Format: monthname day
SELECT FORMAT (GETDATE(), 'M') August 24
O Format: year-month-dateThour:minute:seconds.nanosecond SELECT FORMAT (GETDATE(), 'O') 2021-08-24T12:54:13.5870000
R Format: weekday(abbreviated) date monthname(abbreviated) year hour:minute:seconds GMT SELECT FORMAT (GETDATE(), 'R') Tue, 24 Aug 2021 12:56:29 GMT
s Format: year-month-dateThour:minute:seconds SELECT FORMAT (GETDATE(), 's') 2021-08-24T12:57:41
u Format: year-month-date hour:minute:secondsZ SELECT FORMAT (GETDATE(), 'u') 2021-08-24 12:58:35Z
U Format: weekday, month day, year hour:minute:seconds AM / PM SELECT FORMAT (GETDATE(), 'U') Tuesday, August 24, 2021 4:59:42 AM
t Format: hour:minute AM / PM SELECT FORMAT (GETDATE(), 't') 1:02 PM
T Displays hour, minute, seconds, and AM / PM indicator.
Format: hour:minute:seconds AM / PM
SELECT FORMAT (GETDATE(), 'T') 1:01:03 PM
Y Displays the month name and year.
Format: monthname year
SELECT FORMAT (GETDATE(), 'Y') August 2021


Custom Format Specifiers

#

And these are custom date and time format specifiers for FORMAT.

Format Description Example
d Day of the month: 1 - 31 2021-09-01T13:45:30 -> 1
dd Day of the month: 01 - 31 2021-09-01T13:45:30 -> 01
ddd Abbreviated day of the week: Mon - Sun 2021-09-01T13:45:30 -> Sat
dddd Full day of the week: Monday - Sunday 2021-09-01T13:45:30 -> Saturday
f Tenth of a second 2021-09-15T13:45:30.6175420 -> 6
ff Hundredths of a second 2021-09-15T13:45:30.6175420 -> 61
fff Thousandths of a second 2021-09-15T13:45:30.6175420 -> 617
ffff Ten thousandths of a second 2021-09-15T13:45:30.6175420 -> 6175
fffff Hundred thousandths of a second 2021-09-15T13:45:30.6175420 -> 61754
ffffff Millionths of a second 2021-09-15T13:45:30.6175420 -> 617542
fffffff Ten millionths of a second 2021-09-15T13:45:30.6175420 -> 6175420
F If non-zero, tenth of a second 2021-09-15T13:45:30.6175420 -> 6
2021-09-15T13:45:30.0175420 -> no output
FF If non-zero, hundredths of a second 2021-09-15T13:45:30.6175420 -> 61
2021-09-15T13:45:30.0075420 -> no output
FFF If non-zero, thousandths of a second 2021-09-15T13:45:30.6175420 -> 617
2021-09-15T13:45:30.0005420 -> no output
FFFF If non-zero, ten thousandths of a second 2021-09-15T13:45:30.6175420 -> 6175
2021-09-15T13:45:30.0010420 -> 001
FFFFF If non-zero, hundred thousandths of a second 2021-09-15T13:45:30.6175420 -> 61754
2021-09-15T13:45:30.0000020 -> no output
FFFFFF If non-zero, millionths of a second 2021-09-15T13:45:30.6175420 -> 617542
2021-09-15T13:45:30.0000001 -> no output
FFFFFFF If non-zero, ten millionths of a second 2021-09-15T13:45:30.6175420 -> 6175420
2021-09-15T13:45:30.0005400 -> 00054
g, gg Period or era 2021-09-15T13:45:30.6170000 -> A.D.
h Hour, 1-12 2021-09-15T01:45:30 -> 1
hh Hour, 01-12 2021-09-15T01:45:30 -> 01
H Hour, 1-23 2021-09-15T01:45:30 -> 1
HH Hour, 01-23 2021-09-15T01:45:30 -> 01
K Time zone information 2021-09-15T01:45:30-07:00 --> -07:00
m Minute, 1-59 2021-09-15T01:09:30 -> 9
mm Minute, 01-59 2021-09-15T01:09:30 -> 09
M Month, 1-12 2021-09-15T13:45:30 -> 6
MM Month, 01-12 2021-09-15T13:45:30 -> 06
MMM Abbreviated name of Month: Jan - Dec 2021-09-15T13:45:30 -> Sep
MMMM Full name of Month: January - December 2021-09-15T13:45:30 -> September
s Second, 1-59 2021-09-15T13:45:09 -> 9
ss Second, 01-59 2021-09-15T13:45:09 -> 09
t First letter in AM/PM designator 2021-09-15T13:45:30 -> P
tt AM/PM designator 2021-09-15T13:45:30 -> PM
y Year, 1-99 2009-09-15T13:45:30 -> 9
yy Year, o1-99 2021-09-15T13:45:30 -> 21
yyy Year, with minimum of 3 digits 2021-09-15T13:45:30 -> 2021
yyyy Year, with 4 digits 2021-09-15T13:45:30 -> 2021
yyyyy Year, with 5 digits 2021-09-15T13:45:30 -> 02021
z Hours of offset from UTC 2021-09-15T13:45:30-07:00 -> -7
zz Hours of offset from UTC with leading 0 2021-09-15T13:45:30-07:00 -> -07
zzz Hours and minutes offset from UTC 2021-09-15T13:45:30-07:00 -> -07:00
: Time separator 2021-09-15T13:45:30-07:00 -> :
/ Date separator 2021-09-15T13:45:30-07:00 -> /
"string",
'string'
Literal string delimiter 2021-09-15T13:45:30 ("arr:" h:m t) -> arr: 1:45 P
% Defines subsequent character as a custom format specifier. 2021-09-15T13:45:30 (%mm) -> 45
Any other character Character is copied to the result string unchanged. 2021-09-15T01:45:30 (X hh:mm t) -> X 01:45 A

Example custom date formatters.

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS Date1,
       FORMAT(GETDATE(), 'MMMM dd, yyyy') AS Date2,
       FORMAT(GETDATE(), 'MM.dd.yy') AS Date3,
       FORMAT(GETDATE(), 'MM-dd-yy') AS Date4,
       FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS Date5,
       FORMAT(GETDATE(), 'hh:mm:ss tt') AS Date6
Result:  1 record
Date1 Date2 Date3 Date4 Date5 Date6
09/14/2021 September 14, 2021 09.14.21 09-14-21 2021-09-14 04:23:00 04:23:00 PM

This query ran on Sept 14, 2021.

FORMAT with Dates and Culture

Format the current date for different cultures.
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS 'US', 
       FORMAT(GETDATE(), 'd', 'en-gb') AS 'GB', 
       FORMAT(GETDATE(), 'd', 'de-de') AS 'Germany', 
       FORMAT(GETDATE(), 'd', 'zh-cn') AS 'China)', 
       FORMAT(GETDATE(), 'd', 'hi-IN') AS 'India', 
       FORMAT(GETDATE(), 'd', 'ru-RU') AS 'Russia', 
       FORMAT(GETDATE(), 'd', 'gl-ES') AS 'Galicia (Spain)'
Result:  1 record
US GB Germany China India Russia Galicia (Spain)
11/24/2021 24/11/2021 24.11.2021 2021/11/24 24-11-2021 24.11.2021 24/11/2021

This query ran on Nov 24, 2021.


Formatting currency



PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List the Product prices in different currencies.
SELECT ProductName,
       FORMAT(UnitPrice, 'c', 'en-US') AS 'US', 
       FORMAT(UnitPrice, 'c', 'en-gb') AS 'GB',
       FORMAT(UnitPrice, 'c', 'de-DE') AS 'German', 
       FORMAT(UnitPrice, 'c', 'ru-RU') AS 'Russian', 
       FORMAT(UnitPrice, 'c', 'hi-IN') AS 'Indian'
  FROM Product
Result:  78 records
ProductName US GB German Russian Indian
Chai $18.00 18,00 € £18.00 18,00 ₽ ₹18.00
Chang $19.00 19,00 € £19.00 19,00 ₽ ₹19.00
Aniseed Syrup $10.00 10,00 € £10.00 10,00 ₽ ₹10.00
Chef Anton's Cajun Seasoning $22.00 22,00 € £22.00 22,00 ₽ ₹22.00
Chef Anton's Gumbo Mix $21.35 21,35 € £21.35 21,35 ₽ ₹21.35

Formatting numbers



SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List suppliers with the standard deviation of their product prices in US and German format.
 SELECT CompanyName,  
        FORMAT(STDEV(UnitPrice), 'N', 'en-US') AS 'Stdev US', 
        FORMAT(STDEV(UnitPrice), 'N', 'de-DE') AS 'Stdev German'
   FROM Supplier S
   JOIN Product P ON S.Id = P.SupplierId
  GROUP BY CompanyName
Result:  29 records
CompanyName Stdev US Stdev German
Aux joyeux ecclésiastiques 173.59 173,59
Bigfoot Breweries 2.31 2,31
Cooperativa de Quesos 'Las Cabras' 12.02 12,02
Escargots Nouveaux NULL NULL
Exotic Liquids 4.93 4,93

Notice the difference between the decimal point and comma in the numbers.
Some standard deviations are NULL because these suppliers only have 1 product.


More Examples



ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders from Germany formatted according to their local culture.
SELECT FirstName, LastName, City,
       FORMAT(TotalAmount,'C', 'de-de') AS Amount,
       FORMAT(OrderDate, 'd', 'de-de') AS Date
 FROM [Order] O
 JOIN Customer C ON C.Id = O.CustomerId
WHERE C.Country = 'Germany'
Result:  122 records
FirstName LastName City Amount Date
Rita Müller Stuttgart 1.614,80 € 23.09.2012
Horst Kloss Cunewalde 182,40 € 24.09.2012
Philip Cramer Brandenburg 164,40 € 07.10.2012
Philip Cramer Brandenburg 1.497,00 € 07.10.2012
Peter Franken München 2.467,00 € 24.10.2012
Peter Franken München 2.300,80 € 30.10.2012

FORMAT vs CAST vs CONVERT

#

FORMAT is a newer function that is supported in SQL Server 2012 and higher. It requires .NET CLR (Common Language Runtime) which makes it a bit slower than the traditional CAST and CONVERT functions.

On the other hand FORMAT offers more flexibility in formatting specific values and local-aware formatting.

Tip: As a general rule it is usually better to retrieve raw data from the database and let the client handle data conversions and formatting.


You may also like



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.