FORMAT (Transact-SQL)

value

Expression of a supported data type to format.

format

nvarchar format pattern.

The format 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 is not supported. For a full explanation of these formatting patterns, please consult the.NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. A good starting point is the topic, "Formatting Types."

culture

Optional nvarchar argument specifying a culture.

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

nvarchar or null

The length of the return value is determined by the format.

Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general type conversions, continue to use CAST or CONVERT.

In the case of errors other than a culture that is not valid, such as a format that is not valid, FORMAT returns a null.

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

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

More information about the value parameter

Acceptable data types for the value argument are listed in the following table, together with their .NET Framework mapping information:

A. Simple FORMAT example

DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS Result;

Result
--------
01/01/2011

(1 row(s) affected)

B. FORMAT with custom formatting strings

-- Current date is 01/06/2011
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result'

Result
--------
06/01/2011

(1 row(s) affected)