FORMAT Function for Transact-SQL – T-SQL

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 ] )  

Return Types:  nvarchar or null

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

Remarks: 

 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).

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.

 DateTime Result Custom Number Result
--------------   --------------------  
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

,

No comments yet.

Leave a Reply

*