Format Function in SQL Server

Finally, Microsoft introduced Format function in SQL Server 2012, code name denali that is very handy to format dates, times and currency in different cultures and custom format

In prior versions, we have to create our own function to format number.

Refer related article for prior versions: Format Number in SQL.

In this blog, I am sharing some of the examples of how to format date, time, currency and number with format function.

Format Date with culture
1. DECLARE @DATE DATE = GETDATE()
2. SELECT FORMAT(@DATE, 'd', 'en-US') AS [Date (US)]
3. , FORMAT(@DATE, 'd', 'en-IN') AS [Date (India)]
4. , FORMAT(@@DATE, 'd', 'en-GB') AS [Date (Great Britain)]
5. , FORMAT(@DATE, 'd', 'de-DE') AS [Date (Denmark)]
Output
Format Date with custom format
1. DECLARE @DATE DATETIME = GETDATE()
2. SELECT FORMAT(@DATE, 'dd/MM/yyyy') AS [Date (DD/MM/YYYY)]
3. , FORMAT(@DATE, 'MM/dd/yyyy') AS [Date (MM/DD/YYYY)]
4. , FORMAT(@DATE, 'MM/dd/yyyy ') AS [Date (MM/DD/YYYY)]
5. , FORMAT(@DATE, 'MM/dd/yyyy hh:mm:ss tt') AS [Date (MM/DD/YYYY HH:MM:SS)]
6. , FORMAT(@DATE, 'MM/dd/yyyy HH:mm:ss') AS [Date (MM/DD/YYYY H24:MM:SS)]
7. , FORMAT(@DATE,'ddd') AS [Week Day]
8. , FORMAT(@DATE,'MMM') AS [Short Month Name]
9. , FORMAT(@DATE,'MMMM') AS [Full Month Name]
10. , FORMAT(@DATE,'yyyy') AS [Year]
Output
Format Date with short codes
1. DECLARE @DATE DATETIME = GETDATE()
2. SELECT FORMAT(@DATE,'d') AS [Short date pattern]
3. , FORMAT(@DATE,'D') AS [Long Date pattern]
4. , FORMAT(@DATE,'t') AS [Short Time pattern]
5. , FORMAT(@DATE,'T') AS [Long Time pattern]
Output
Format Currency with culture
1. DECLARE @Amount MONEY = 210525.52;
2. SELECT FORMAT(@Amount,'c','en-US') [Money (US)]
3. , FORMAT(@Amount,'c','en-IN') [Money (India)]
4. , FORMAT(@Amount,'c','en-GB') [Money (Great Britain)]
5. , FORMAT(@Amount,'c','fr') [Money (France)]
6. , FORMAT(@Amount,'c','de-DE') [Money (Denmark)]
7. , FORMAT(@Amount,'c','ru-RU') [Money (Russia)]
Output
Format Percentage
1. DECLARE @Per DECIMAL(4,4) = 0.5545;
2. SELECT FORMAT(@Per,'p0') [Percentage (Without decimal)]
3. , FORMAT(@Per,'p1') [Percentage (With 1 decimal)]
4. , FORMAT(@Per,'p2') [Percentage (With 2 decimal)]
Output
Format Number
1. DECLARE @num FLOAT=1234567.89
2. SELECT FORMAT(@num,'N') AS 'Format Number Defaul'
3. , FORMAT(@num,'#,#.00') AS 'Format Number with 2 decimal'
4. , FORMAT(@num,'#,#.0') AS 'Format Number with 1 decimal'
Output