Format Function in Sql Server 2012

Finally, Microsoft introduced Format function in SQL Server 2012, 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 post, I am sharing some 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)]

RESULT

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]

RESULT

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]

RESULT

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

RESULT

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

RESULT

Format Number

DECLARE @num FLOAT=1234567.89
SELECT    FORMAT(@num,'N') AS 'Format Number Default'
        , FORMAT(@num,'#,#.00') AS 'Format Number with 2 decimal'
        , FORMAT(@num,'#,#.0') AS 'Format Number with 1 decimal'

RESULT