Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings.
FORMAT ( value, format [, culture ] )
The length of the return value is determined by the format.
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.
FORMAT relies upon CLR formatting rules which dictate that colons and periods must be escaped. Therefor, 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.
The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent 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 |
A. Simple FORMAT example
The following example returns a simple date formatted for different cultures.
DECLARE @d DATETIME = '10/01/2011'; SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';
B. FORMAT with custom formatting strings
-- Current date is September 27 2012. DECLARE @d DATETIME = GETDATE(); SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result';
Here is the result set.
-------------- -------------------- 27/09/2012 123-45-6789 (1 row(s) affected)
C. FORMAT with numeric types
SELECT TOP(5)CurrencyRateID, EndOfDayRate ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number 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.
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 (5 row(s) affected)
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.CurrencyRate
ORDER BY CurrencyRateID;
D. FORMAT with time data types
FORMAT returns NULL in these cases because . and : are not escaped.
SELECT FORMAT(cast('07:35' as time), N'hh.mm'); --> returns NULL
SELECT 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.35
SELECT FORMAT(cast('07:35' as time), N'hh\:mm'); --> returns 07:35