SQL FORMAT Function

The SQL FORMAT function is used to format values in the specified format using the optional culture parameter value. FORMAT is one of the new built-in String Function introduced as a Part of Sql Server 2012. It is not an Sql Server native function instead it is .NET CLR dependent function. Return value type is nvarchar.

SYNTAX

FORMAT ( value, format [, culture ] )

where,
value – Value to be formatted
format – This parameter specifies the format in which the vlaue will be formatted.
culture – This parameter is optional, it specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.

FORMAT DATE with Culture

DECLARE @date DATETIME = GETDATE() 
SELECT @date AS 'GETDATE()',
       FORMAT( @date, 'd', 'en-US') AS 'DATE IN US Culture',
       FORMAT( @date, 'd', 'en-IN') AS 'DATE IN INDIAN Culture',
       FORMAT( @date, 'd', 'de-DE') AS 'DATE IN GERMAN Culture'

 Output

output1 of FORMAT DATE with Culture

FORMAT CURRENCY with Culture

DECLARE @Price INT = 40
SELECT FORMAT(@Price,'c','en-US') 
         AS 'CURRENCY IN US Culture',       
    FORMAT(@Price,'c','de-DE')
         AS 'CURRENCY IN GERMAN Culture'

Output

output2 of FORMAT CURRENCY with Culture

FORMAT CURRENCY

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default',
      FORMAT( @Price, 'C0') AS 'With 0 Decimal',
       FORMAT( @Price, 'C1') AS 'With 1 Decimal',
       FORMAT( @Price, 'C2') AS 'With 2 Decimal',
       FORMAT( @Price, 'C3') AS 'With 3 Decimal'

Output

output3 of FORMAT CURRENCY

FORMAT PERCENTAGE

DECLARE @Percentage float = 0.35674
SELECT FORMAT( @Percentage, 'P') AS '% Default',
       FORMAT( @Percentage, 'P0') AS '% With 0 Decimal',
       FORMAT( @Percentage, 'P1') AS '% with 1 Decimal',
       FORMAT( @Percentage, 'P2') AS '% with 2 Decimal',
       FORMAT( @Percentage, 'P3') AS '% with 3 Decimal'

Output

output4 of FORMAT PERCENTAGE

FORMAT NUMBER

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US',
    FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA'
 
SELECT FORMAT( @Number, '#.0')     AS 'With 1 Decimal',
    FORMAT( @Number, '#.00')    AS 'With 2 Decimal',
    FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal',
    FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal'

Output

output5 of FORMAT NUMBER