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




How to use FORMAT() function in Sql Server How to use FORMAT() function in Sql Server Reviewed by Alok Singh on 06:51 Rating: 5
Powered by Blogger.