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
  

  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