Applies to:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric

Returns a value formatted with the specified format and optional culture. Use theFORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, useCAST orCONVERT.

Transact-SQL syntax conventions

Syntax

FORMAT( value , format [ , culture ] )

Arguments

value

Expression of a supported data type to format. For a list of valid types, see the table in theRemarks section.

format

nvarchar format pattern.

Theformat argument must contain a valid .NET Framework format string, either as a standard format string (for example,"C" or"D"), or as a pattern of custom characters for dates and numeric values (for example,"MMMM DD, yyyy (dddd)"). Composite formatting isn't supported.

For a full explanation of these formatting patterns, consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. For more information, seeFormatting Types.

culture

Optionalnvarchar argument specifying a culture.

If theculture argument isn't provided, the language of the current session is used. This language is set either implicitly, or explicitly by using theSET LANGUAGE statement.culture accepts any culture supported by the .NET Framework as an argument; it isn't limited to the languages explicitly supported by SQL Server. If theculture argument isn't valid,FORMAT raises an error.

Return types

nvarchar or null

The length of the return value is determined by theformat.

Remarks

FORMAT returnsNULL for errors other than aculture that isn'tvalid. For example,NULL is returned if the value specified informat isn't valid.

TheFORMAT function is nondeterministic.

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

This function can't be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR, could cause an error on the remote server.

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 thetime data type. SeeD. FORMAT with time data types.

The following table lists the acceptable data types for thevalue argument together with their .NET Framework mapping equivalent types.

Examples

A. Simple FORMAT example

The following example returns a simple date formatted for different cultures.

DECLARE @d AS DATE = '08/09/2024';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)';

Here's the result set.

US English   British English  German      Simplified Chinese (PRC)-----------  ---------------- ----------- -------------------------8/9/2024     09/08/2024       09.08.2024  2024/8/9US 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 formatting strings

The following example shows formatting numeric values by specifying a custom format. The example assumes that the current date is August 9, 2024. For more information about these and other custom formats, seeCustom Numeric Format Strings.

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

Here's the result set.

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

C. FORMAT with numeric types

The following example returns five rows from theSales.CurrencyRate table in theAdventureWorks2022 database. The columnEndOfDateRate is stored as typemoney in the table. In this example, the column is returned unformatted and then formatted by specifying the .NET Number format, General format, and Currency format types. For more information about these and other numeric formats, seeStandard 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.CurrencyRateORDER BY CurrencyRateID;

Here's the result set.

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format-------------- ------------  --------------  --------------  ---------------1              1.0002        1.00            1.0002          $1.002              1.55          1.55            1.5500          $1.553              1.9419        1.94            1.9419          $1.944              1.4683        1.47            1.4683          $1.475              8.2784        8.28            8.2784          $8.28

This example specifies 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.CurrencyRateORDER BY CurrencyRateID;

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

FORMAT returnsNULL in these cases because. and: aren't escaped.

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

Format returns a formatted string because the. and: are escaped.

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

Format returns a formatted current time with AM or PM specified.

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt'); --> returns 03:46 PMSELECT FORMAT(SYSDATETIME(), N'hh:mm t');  --> returns 03:46 P

Format returns the specified time, displaying AM.

SELECT FORMAT(CAST('2018-01-01 01:00' AS DATETIME2), N'hh:mm tt'); --> returns 01:00 AMSELECT FORMAT(CAST('2018-01-01 01:00' AS DATETIME2), N'hh:mm t');  --> returns 01:00 A

Format returns the specified time, displaying PM.

SELECT FORMAT(CAST('2018-01-01 14:00' AS DATETIME2), N'hh:mm tt'); --> returns 02:00 PMSELECT FORMAT(CAST('2018-01-01 14:00' AS DATETIME2), N'hh:mm t');  --> returns 02:00 P

Format returns the specified time in 24-hour format.

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

E. Format with comma separators for large numbers

The following example shows how to format large numbers with comma separators.

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

Here's the result set.

1,234,568

This example uses theN format specifier. TheN specifier is used for numeric values, and the number of decimal places can be adjusted by changing the format string (for example,N2 for two decimal places).

FORMAT ( value, format_string [, culture ] )

Parameters

• value: The value to format.
• format_string: A string that specifies the format to apply.
• culture: (Optional) A string that specifies the culture to use for formatting.

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

1,234,568

Related content

• CAST and CONVERT (Transact-SQL)
• STR (Transact-SQL)
• String Functions (Transact-SQL)