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

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Share this

Related Posts

Previous
Next Post »