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
