Mar 25, 2012

FORMAT function in sql server 2012



Sql server 2012 has introduced a function FORMAT to convert data is specified format.

Syntax:

FORMAT (<Data>, <Format> [,<Culture> ] )

Note:  <Culture> is optional parameter

Examples:

1. To convert the numeric data in the currency format according to en-Us culture.

SELECT FORMAT (100, 'C', 'en-US') AS Value

Output:

Value
$25.00
  
2. To convert the numeric data in the currency format according to fr-FR culture.

SELECT FORMAT (25, 'C', 'fr-FR') AS Value

Output:

Value
25,00 €

3.

DECLARE @dDATETIME = '01/01/2011';

SELECT
FORMAT(@d, 'd', 'de-DE') AS German,
FORMAT(@d ,'d','fr-FR') AS French,
FORMAT(@d,'d','ja-JP') AS Japan

Output: 

German
French
Japan
01.01.2011
01/01/2011
2011/01/01



1. All the formats in sql server 2012

Numeric

2. Currency Format of all the cultures in sql server 2012
3. Decimal, exponential and fixed points format of all the cultures in sql server 2012
4. Number, general and percent format of all the cultures in sql server 2012
5. Round-trip and hexadecimal format of all the cultures in sql server 2012

Date and time

6. Short data and long date format of all the cultures in sql server 2012
7. Full Date Short Time and Full Date long Time format of all the cultures in sql server 2012
8. General Date Short Time and General Date long Time format of all the cultures in sql server 2012
9. Month Day and Year Month format of all the cultures in sql server 2012
10. Round-trip, RFC1123 and Sortable Date Time format of all the cultures in sql server 2012
11. Short Time and Long Time format of all the cultures in sql server 2012
12. Universal Sortable Date Time and Universal Full Date Time format of all the cultures in sql server 2012

No comments:

Post a Comment