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.
This example formats a number as currency.
SELECT FORMAT(70.95,'c') AS Price
Price |
---|
$70.95 |
FORMAT
supports locale-aware formatting of date/time and number values as strings.
Alternatively, use CAST and CONVERT for other data type conversions.
Syntax for the FORMAT function.
- 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.
Return the current date as 'month day'.
SELECT FORMAT( GETDATE(), 'M' ) AS Today
Today |
---|
December 21 |
Dates and times can be formatted with standard and custom 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 |
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
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.
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)'
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.
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
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
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 |
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
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
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.
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
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'
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
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.