SQL FORMAT Function

This example formats a number as currency.

SELECT FORMAT(70.95,'c') AS Price

Try it live

---

Syntax for the FORMAT function.

FORMAT (value, format [,culture])

See below for value, format, and culture examples.

---

Return the current date as 'month day'.

SELECT FORMAT( GETDATE(), 'M' ) AS Today

Try it live

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

The standard date and time format specifiers for FORMAT.

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

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

Try it live

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)'

Try it live

This query ran on Nov 24, 2021.

---

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

Try it live

---

 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

Try it live

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

---

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'

Try it live

---

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.

---