Wednesday, August 28, 2019

SQL Server - FORMAT() function in T-SQL

In SQL Server 2012 and later, there is the FORMAT() function which is responsible to format a value with the specified format such as format date/time values and number values.
Before this function,  developers usually format the values as a percentage manually.
This function accepts three arguments as given below syntax- 

FORMAT(value, format, culture)

Parameter Description
value:  Required. The value to be formatted
format:  Required. The format pattern
culture:  Optional. Specifies a culture (from SQL Server 2017)

examples -
  1. Country Specific Date Conversation,
  2. Custom Numeric Format- This consists of a series of custom numeric format specifiers that provide a pattern for your number.
  3. Format Number to Percentage - If you are trying to simply format a number as a percent with two decimal places

Example  - The following example returns a simple date formatted for different cultures.
DECLARE @d DATETIME = '10/01/2019'
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'USA' 
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain' 
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German' 
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'China';
 
USA
Great Britain
German
China
10/1/2019
01/10/2019
01.10.2019
2019/10/1

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'; 
USA
Great Britain
German
China
Saturday, October 01, 2019
01 October 2019
Samstag, 1. Oktober 2019
2011101



Benefit of this is you can control additional things like thousand separators and you don't get that space between the number and '%'.
Remarks
  1. 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.
  2. The FORMAT function is nondeterministic.
  3. FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).
  4. This function cannot 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.
  5. 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 the time data type. 

To Learn more, please visit our blog at - 
http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at - 
http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
https://twitter.com/macxima

1 comment:

  1. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command...
    MySQL Online Training
    MySQL Online Certification
    MySQL Course Certification

    ReplyDelete