SQL Server Denali CTP3 brings a couple of new functions, one of these is the FORMAT function

The syntax of the format function looks like this

FORMAT ( value, format [, culture ] )

Here is what Books On Line has to say about the arguments that you can pass in

_value

Expression of a supported data type to format.

format

nvarchar format pattern.

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, “C” or “D”), or as a pattern of custom characters for dates and numeric values (for example, “MMMM dd, yyyy (dddd)”). Composite formatting is not supported. For a full explanation of these formatting patterns, please consult the.NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. A good starting point is the topic, “Formatting Types.“

culture

Optional nvarchar argument specifying a culture.

If the culture argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.

Before we continue, I recommend that you visit the National Language Support (NLS) API Reference page to see all the locales that are available

If you are a .NET programmer then this function should look very familiar to you

Let’s take a look at how it all works, first let’s create a table and inserts some locales info so that it will be easier to show the different output later

CREATE TABLE Locales(locale varchar(100))
insert Locales
select 'en-US'   --USA
union
select 'nl' --Netherlands
union
select 'fr'  --France
union
select 'de' --Germany
union
select 'no'  --Norway
union
select 'ru' --Russia

Now, let’s format some dates

DECLARE @d DATETIME = '01/01/2011';

select locale,FORMAT ( @d, 'd', locale ) AS Result,
              FORMAT( @d, 'yyyy-MM-dd', locale ) Result2
from Locales

Here is what the output looks like, as you can see if you use specific formatting, the output is the same no matter what the locale is

Let’s look at another example, this one will format currency

select locale,FORMAT ( 100, 'c', locale ) AS Result
from Locales

As you can see the currency symbol is different depending on what locale has been used, the symbol will also alternate between the end or the start of the output depending again on the locale

You can also specify the number of characters after the decimal point

select locale,FORMAT ( 100.34, 'C1', locale ) AS Result1,
			  FORMAT ( 100.34, 'C2', locale ) AS Result2,
			  FORMAT ( 100.34, 'C3', locale ) AS Result3,
			  FORMAT ( 100.34, 'C4', locale ) AS Result4
from Locales

There you have it, formatted exactly like you want. I think the FORMAT function is a welcome addition, it will make formatting much easier than before when we have to mess around with CAST or CONVERT and style arguments