How to use FORMAT() function in Sql Server

Sql FORMAT() - Function  

FORMAT function is new feature in Sql server 2012 that formats in specific format with defined culture and return as formatted string.

Syntax: - FORMAT (Input_value, format, culture)

Return Type:  this returns nvarchar

SELECT FORMAT(GETDATE(),'yyyy-MM-dd')

Following are Different type of Example that will be helpful for you.

Example 1:- FORMAT NUMBER with Culture

DECLARE @Number AS DECIMAL(10,2) = 57871.984

SELECT FORMAT( @Number, 'N','en-US') AS 'US Number Format',

       FORMAT( @Number, 'N','en-IN')  AS 'INDIA Number Format',

       FORMAT( @Number, 'N','en-AU')  AS 'AU Number Format'

 Result:- It gives below output

Example 2:- FORMAT NUMBER with Decimal Place And With Culture

DECLARE @Number AS DECIMAL(10,2) = 57871.984

SELECT FORMAT( @Number, '#.0')     AS 'Decimal 1 place',

       FORMAT( @Number, '#.00')    AS 'Decimal 2 place'

      

SELECT FORMAT( @Number, '#,##.00') AS 'With Comma and Decimal 2 place',

       FORMAT( @Number, '##.00')   AS 'Without Comma and Decimal 2 place

 Result:- It gives below output

Example 3:- FORMAT With CUSTOM DATE

DECLARE @date DATETIME = GETDATE()

SELECT @date AS 'GETDATE()',

    FORMAT ( @date, 'dd-MM-yyyy') AS 'dd-MM-yyyy',

    FORMAT ( @date, 'dd/MM/yyyy') AS 'dd/MM/yyyy',

    FORMAT ( @date, 'MM/dd/yyyy') AS 'MM/dd/yyyy',

    FORMAT ( @date, 'yyyy/MM/dd') AS 'yyyy/MM/dd'

 Result:- It gives below output

 Example 4:- FORMAT With CUSTOM DATE with Culture

DECLARE @date DATETIME = GETDATE()

SELECT

 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')  AS 'US',

 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN')  AS 'Hindi',

 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','pa-IN')  AS 'Punjabi',

 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','sa-IN')  AS 'Sanskrit'

 Result:- It gives below output

 Example 5:- FORMAT WITH CURRENCY

  --FORMAT WITH CURRENCY

 DECLARE @Amount DECIMAL(5,3) = 98.356

SELECT FORMAT( @Amount, 'C') AS 'Default',

       FORMAT( @Amount, 'C0') AS 'Decimal 0 place',

       FORMAT( @Amount, 'C1') AS 'Decimal 1 place',

       FORMAT( @Amount, 'C2') AS 'Decimal 2 place',

       FORMAT( @Amount, 'C3') AS 'Decimal 3 place'

 Result:- It gives below output

 Example 6:- FORMAT CURRENCY with Culture

  

DECLARE @Price INT = 100

SELECT FORMAT(@Price,'c','en-US') AS 'US CURRENCY',      

       FORMAT(@Price,'c','de-DE') AS 'GERMAN CURRENCY',

       FORMAT(@Price,'c','ar-OM') AS 'OMAN CURRENCY',

       FORMAT(@Price,'c','en-IN') AS 'INDIAN CURRENCY'

 Result:- It gives below output

Example 7:- FORMAT DATE with Culture

DECLARE @date DATETIME = GETDATE()

SELECT @date AS 'GETDATE()',

       FORMAT( @date, 'd', 'en-US') AS 'US CULTURE DATE',

       FORMAT( @date, 'd', 'en-IN') AS 'INDIAN CULTURE DATE',

       FORMAT( @date, 'd', 'de-DE') AS 'GERMAN CULTURE DATE'

Result:- It gives below output

Example 8:- FORMAT PERCENTAGE

DECLARE @Percentage float = 0.874545

SELECT FORMAT( @Percentage, 'P') AS '% Default',

       FORMAT( @Percentage, 'P0') AS '% With Decimal 0 Place',

       FORMAT( @Percentage, 'P1') AS '% with Decimal 0 Place',

       FORMAT( @Percentage, 'P2') AS '% with Decimal 0 Place',

       FORMAT( @Percentage, 'P3') AS '% with Decimal 0 Place'

 Result:- It gives below output