The Format() Function in SQL Server 2012

Folks with a programming background use some kind of function or module to format the output of dates, numbers or text. The only formatting capabilities that SQL Server has had until now was the CAST and CONVERT function.  Some programmers write user defined functions for dates as shown in Formatted date in SQL Server 2008.

The long wait for this format function is over. The new SQL Server 2012 RC0 is leveraging the .NET format capabilities by introducing the FORMAT() function.

In this article, I am going to explore and illustrate the features of the FORMAT() function.

The function FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where you pass the date value or numeric value. The second parameter is the.NET Framework format string. The format parameter is case sensitive. “D” doesn’t mean the same as “d”.  The third parameter is the culture. This can be any culture supported by the .NET Framework.

Formatting the DATE

Let us see some examples with DATE as  the value by changing the format.

Example 1:

To display weekday date name, month name and the day with year, you could use the following:

DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'D', 'en-US' ) AS FormattedDate;

Result:

Wednesday, December 21, 2011

Example 2:

To display only the month name and day, you could use the following example.

DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'm', 'en-US' ) AS FormattedDate;

Result:

December 21

Example 3

To display long date and time use the following example.

DECLARE @date DATETIME = getdate();
SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ) AS FormattedDate;

Result:

2011/12/21 11:49:00 PM

DECLARE
@date DATETIME = convert(datetime,'2011/01/01 2:00:00');
SELECT
FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss t','en-US' ) AS FormattedDate;

2011/01/01 02:00:00 A

DECLARE
@date DATETIME = convert(datetime,'2011/01/01 14:00:00');
SELECT
FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss t','en-US' ) AS FormattedDate;

FormattedDate

2011/01/01 02:00:00 P

DECLARE
@date DATETIME = convert(datetime,'2011/01/01 2:00:00');
SELECT
FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt','en-US' ) AS FormattedDate;

2011/01/01 02:00:00 AM

DECLARE
@date DATETIME = convert(datetime,'2011/01/01 14:00:00');
SELECT
FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt','en-US' ) AS FormattedDate;

2011/01/01 02:00:00 PM

Example 4:

The following example displays the time.

DECLARE @date DATETIME = getdate();
SELECT FORMAT ( @date, 'h\:m\:ss\.ffffff', 'en-US' ) AS FormattedDate;

Result:

12:10:15.453000

Reference

The format string for the date value can be a standard date format or a custom date format or even a custom time span formatting.

• standard date format
• custom date time format
• custom time span formatting

Now let’s use the same date values and change the culture value parameter. For example, if I want to display the Date name, Month name and the day in different culture format the culture format is very useful.

Example 1:

This example displays the date in Taiwan format. Taiwan uses traditional Chinese characters.

DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'MMMM dddd d', 'zh-TW' ) AS FormattedDate;

Result:

Example2:

This example displays the date using Tamil culture.

DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'MMMM dddd d', 'ta-IN' ) AS FormattedDate;

Result

Note:

Please refer to the following links to learn more about the different language and culture usage.

• CultureInfo Class – (.NET Framework 2.0)
• CultureInfo Class – (Silverlight)

Formatting the NUMBERS

Let’s see some of the numeric formats that we can display using the FORMAT() function. Personally, I format numbers for currencies and number of decimal characters or for percentage. Here are some examples.

To display the number with currency using locale, use the following example.

Example 1:

DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C') AS MyMoney;

Result:

$125,000.00

Here, I am getting the currency symbol ‘$’ because my current locale language setting is en-us. I could also display the currency ‘$’ explicitly by using the culture parameter as shown below.

DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C', 'en-US'  ) AS MyMoney;

Example 2:

If I change the culture parameter to Tamil culture, the FORMAT function will automatically change the currency to the Tamil currency Indian Rupee (in Tamil language pronounced “Roopai”). You can also see the separator is placed differently.

DECLARE @money money = '125000';
SELECT FORMAT ( @money,'C', 'ta-IN') AS MyMoney;

Result

ரூ 1,25,000.00

Example 3:

If I change the culture parameter to Taiwan culture, the FORMAT function will automatically change the currency to ‘NT’ meaning New Taiwan dollar.

DECLARE @money money = '125000';
SELECT FORMAT ( @money,'C', 'zh-TW') AS MyMoney;

Result:

NT$125,000.00

Example 4:

I could also change my language setting first and display the currency as shown below.

set language british
DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C') AS MyMoney;

Result

£125,000.00

Example 5:

The following example displays the number in percentage.

DECLARE @num bigint = 5
SELECT FORMAT ( @num, '00.000%') AS MyMoney;

Result:

500.000%

Some other examples of using the format function are listed below. These are self-explanatory.

Example set 1:

SELECT Format(1222.4, '##,##0.00')   -- Returns "1,222.40".
SELECT Format(345.9, '###0.00')   -- Returns "345.90".
SELECT Format(15, '0.00%')   -- Returns "1500.00%".

Example set 2

DECLARE @date datetime =getdate()
SELECT  Format(@date , 'h:m:s')   -- Returns "1:10:47".
SELECT Format(@date , 'hh:mm:ss tt')   --Returns "01:10:47 AM".
SELECT Format(@date , 'dddd, MMM d yyyy')   --Returns "Thursday, Dec 22 2011"

Example Set 3:

SELECT  Format(getdate(), 'M/d/yyyy H:mm tt','en-US') --Returns 12/22/2011 1:14 AM
SELECT  Format(getdate(), 'M/d/yyyy H:mm tt zzz','en-US') --12/22/2011 1:15 AM -05:00

Conclusion:

As mentioned in the beginning of this article, I have illustrated the various standard and custom formats of both DATE values and NUMERIC values using the new FORMAT() function released in SQL Server 2012 RC0.

See all articles by MAK