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