SQL Server Format() Function in detail with example

Returns a NVARCHAR value formatted with the specified format and culture (if specified). This is primarily used for converting date-time types to strings.

Parameters:

1. value. An expression of a supported data type to format. valid types are listed below.

2. format. An NVARCHAR format pattern. See Microsoft official documentation for standard and custom format strings.

3. culture. Optional. nvarchar argument specifying a culture. The default value is the culture of the current session.

DATE

DECLARE @d DATETIME = '2021-07-31';
SELECT
 FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2021'
,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2021'
,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2021'
,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2021/7/31'
,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Saturday, July 31, 2021'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2021'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Samstag, 31. Juli 2021'

Using custom format strings:

SELECT 
 FORMAT ( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2021'
,FORMAT (123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789'
,FORMAT ( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Saturday, July 31, 2021 12:00:00 AM'
,FORMAT ( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns 'शनिवार, जुलाई 31, 2021 12:00:00 पूर्वाह्न'
,FORMAT ( @d, 'dddd', 'en-US' ) AS 'US' -- Returns 'Saturday'
,FORMAT ( @d, 'dddd', 'hi-IN' ) AS 'Hindi' -- Returns 'शनिवार'

FORMAT can also be used for formatting CURRENCY,PERCENTAGE and NUMBERS.

CURRENCY

DECLARE @Price1 INT = 40
SELECT 
 FORMAT (@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'
,FORMAT (@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'

We can specify the number of digits after the decimal.

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT 
FORMAT( @Price, 'C') AS 'Default',        -- Returns '$40.36'
FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
FORMAT( @Price, 'C2') AS 'With 2 Decimal' -- Returns '$40.36'

PERCENTAGE

DECLARE @Percentage float = 0.35674
SELECT 
FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
FORMAT( @Percentage, 'P1') AS '% with 1 Decimal' -- Returns '35.7 %'

NUMBER

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT 
FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
FORMAT( @Number, 'N','en-IN') AS 'Number Format in INDIA', -- Returns '4,54,545.39'
FORMAT( @Number, '#.0') AS 'With 1 Decimal', -- Returns '454545.4'
FORMAT( @Number, '#.00') AS 'With 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
FORMAT( @Number, '##.00') AS 'Without Comma and 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '000454545'

Important Notes:

• FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.
• FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).
• FORMAT relies upon CLR formatting rules which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

Related Article:

Date and Time formatting using Format() function in SQL Server