SQL Server FORMAT Function: Mastering Date and Number Formatting

The FORMAT function in SQL Server is a powerful tool for displaying values in a specific format, incorporating culture-sensitive formatting for dates, times, and numbers as strings. While CAST and CONVERT are suitable for general data type conversions, FORMAT excels when you need precise control over the presentation of your data, especially for localized applications.

Transact-SQL Syntax Conventions

Syntax

FORMAT( value , format [ , culture ] )

Arguments

value

An expression of a supported data type that you want to format. Refer to the Remarks section for a table listing valid types.

format

An nvarchar format pattern.

Composite formatting, such as "MMMM DD, yyyy (dddd)", is not supported.

For a comprehensive understanding of these format patterns, consult the .NET Framework documentation on formatting strings in general, custom date and time formats, and custom numeric formats. For more in-depth information, see Formatting Types.

culture

An optional nvarchar argument that specifies a culture.

If the culture argument is omitted, the language of the current session is used. This language is set implicitly or explicitly using the SET LANGUAGE statement. The culture argument accepts any culture supported by the .NET Framework as an argument and is not restricted to languages explicitly supported by SQL Server. If the culture argument is invalid, FORMAT will raise an error.

Return Types

nvarchar or null

The length of the returned value is determined by format.

Remarks

FORMAT returns NULL for errors, except for an invalid culture. For instance, if the format value is invalid, NULL is returned.

The FORMAT function is non-deterministic. This means that return values are not guaranteed to be the same for successive calls with the same input values. For more information about deterministic and non-deterministic functions, see Deterministic and Nondeterministic Functions.

FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).

This function cannot be remoted because it depends on the CLR’s presence. Remoting a function that requires CLR would fail on the remote server.

FORMAT is based on CLR formatting rules, which require colons and periods to be escaped. Therefore, when your format string (the second parameter) contains a colon or period, you must escape these characters with a backslash when the input value (the first parameter) is of the time data type. See Example D. FORMAT with Time Data Types.

The following table outlines the acceptable data types for the value argument, along with their corresponding .NET Framework mapping types.

Category	Type	.NET Type
Numeric	bigint	`Int64`
Numeric	int	`Int32`
Numeric	smallint	`Int16`
Numeric	tinyint	`Byte`
Numeric	decimal	`SqlDecimal`
Numeric	numeric	`SqlDecimal`
Numeric	float	`Double`
Numeric	real	`Single`
Numeric	smallmoney	`Decimal`
Numeric	money	`Decimal`
Date and Time	date	`DateTime`
Date and Time	time	`TimeSpan`
Date and Time	datetime	`DateTime`
Date and Time	smalldatetime	`DateTime`
Date and Time	datetime2	`DateTime`
Date and Time	datetimeoffset	`DateTimeOffset`

Examples

Let’s explore practical examples of using the FORMAT function to format dates, numbers, and times in SQL Server.

A. Basic FORMAT Example for Date Formatting

This example demonstrates simple date formatting for different cultures using the ‘d’ format specifier (Short date pattern).

DECLARE @d AS DATE = '2024-08-09';

SELECT
    FORMAT(@d, 'd', 'en-US') AS 'US English',
    FORMAT(@d, 'd', 'en-gb') AS 'British English',
    FORMAT(@d, 'd', 'de-de') AS 'German',
    FORMAT(@d, 'd', 'zh-cn') AS 'Chinese Simplified (PRC)';

SELECT
    FORMAT(@d, 'D', 'en-US') AS 'US English',
    FORMAT(@d, 'D', 'en-gb') AS 'British English',
    FORMAT(@d, 'D', 'de-de') AS 'German',
    FORMAT(@d, 'D', 'zh-cn') AS 'Chinese Simplified (PRC)';

This will produce the following result set, showcasing how the date format changes based on the specified culture:

US English    British English German       Chinese Simplified (PRC)
------------- --------------- ------------ -------------------------
8/9/2024      09/08/2024      09.08.2024   2024/8/9

US English                 British English      German                   Chinese (Simplified PRC)
-------------------------- ------------------ ------------------------ -------------------------
Friday, August 9, 2024   09 August 2024     Freitag, 9. August 2024  2024年8月9日

B. FORMAT with Custom Format Strings

The following example shows how to format numeric values using custom format strings. It also shows date formatting. The example assumes the current date is August 9, 2024. For more details on custom formats, refer to .NET documentation on custom numeric format strings.

DECLARE @d AS DATE = GETDATE();

SELECT
    FORMAT(@d, 'dd/MM/yyyy', 'en-US') AS 'Date',
    FORMAT(123456789, '###-##-####') AS 'Custom Number';

This will output:

Date        Custom Number
----------- --------------
09/08/2024  123-45-6789

C. FORMAT with Numeric Types

This example retrieves five rows from the Sales.CurrencyRate table in the AdventureWorks2022 database. The EndOfDayRate column, stored as the money type, is formatted using different .NET numeric formats: Number (‘N’), General (‘G’), and Currency (‘C’). For more information on these and other numeric formats, see .NET documentation on standard numeric format strings.

SELECT TOP (5)
    CurrencyRateID,
    EndOfDayRate,
    FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Numeric Format',
    FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format',
    FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Here is the result set, showing different numeric formats for US English:

CurrencyRateID EndOfDayRate Numeric Format General Format Currency Format
-------------- ------------ -------------- -------------- ---------------
1              1.0002       1.00           1.0002         $1.00
2              1.55         1.55           1.5500         $1.55
3              1.9419       1.94           1.9419         $1.94
4              1.4683       1.47           1.4683         $1.47
5              8.2784       8.28           8.2784         $8.28

Now, let’s see the same example with the German culture (‘de-de’):

SELECT TOP (5)
    CurrencyRateID,
    EndOfDayRate,
    FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format',
    FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format',
    FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

The output reflects German cultural conventions for number and currency formatting:

CurrencyRateID EndOfDayRate Numeric Format General Format Currency Format
-------------- ------------ -------------- -------------- ---------------
1              1.0002       1,00           1,0002         1,00 €
2              1.55         1,55           1,5500         1,55 €
3              1.9419       1,94           1,9419         1,94 €
4              1.4683       1,47           1,4683         1,47 €
5              8.2784       8,28           8,2784         8,28 €

D. FORMAT with Time Data Types

In these examples, FORMAT returns NULL because the period (.) and colon (:) are not escaped in the format string when used with TIME data type.

SELECT FORMAT(CAST('07:35' AS TIME), N'hh.mm');   -- Returns NULL
SELECT FORMAT(CAST('07:35' AS TIME), N'hh:mm');   -- Returns NULL

To correctly format time with periods and colons, escape these characters using a backslash ().

SELECT FORMAT(CAST('07:35' AS TIME), N'hh.mm');  -- Returns 07.35
SELECT FORMAT(CAST('07:35' AS TIME), N'hh:mm');  -- Returns 07:35

This example demonstrates formatting the current time with AM/PM indicators using tt and t format specifiers.

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt');        -- Returns time like "03:46 PM"
SELECT FORMAT(SYSDATETIME(), N'hh:mm t');         -- Returns time like "03:46 P"

Formatting a specific time in DATETIME2 format, showing AM:

SELECT FORMAT(CAST('2018-01-01 01:00' AS DATETIME2), N'hh:mm tt'); -- Returns "01:00 AM"
SELECT FORMAT(CAST('2018-01-01 01:00' AS DATETIME2), N'hh:mm t');  -- Returns "01:00 A"

Formatting a specific time in DATETIME2 format, showing PM:

SELECT FORMAT(CAST('2018-01-01 14:00' AS DATETIME2), N'hh:mm tt'); -- Returns "02:00 PM"
SELECT FORMAT(CAST('2018-01-01 14:00' AS DATETIME2), N'hh:mm t');  -- Returns "02:00 P"

Displaying time in 24-hour format using ‘HH’:

SELECT FORMAT(CAST('2018-01-01 14:00' AS DATETIME2), N'HH:mm');    -- Returns "14:00"

E. Formatting Large Numbers with Comma Separators

This example illustrates how to format large numbers with comma separators for better readability.

SELECT FORMAT(1234567.89, 'N0') AS FormattedNumber;

This will produce the following output:

FormattedNumber
----------------
1,234,568

In this case, the ‘N’ format specifier (Number) is used. ‘N0’ specifies no decimal places. You can adjust the number of decimal places by changing the format string (e.g., ‘N2’ for two decimal places).

FORMAT ( value, format_string [, culture ] )

Parameters

value: The value to be formatted.
format_string: A string specifying the format to apply.
culture: (Optional) A string specifying the culture to use for formatting.

SELECT FORMAT(1234567.89, 'N0') AS FormattedNumber;

FormattedNumber
----------------
1,234,568