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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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

T-SQL
1
2
3
4
5
DECLARE @d DATETIME = '01/01/2011';
 
select locale,FORMAT ( @d, 'd', locale ) AS Result,
              FORMAT( @d, 'yyyy-MM-dd', locale ) Result2
from Locales
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

locale Result Result2
de 01.01.2011 2011-01-01
en-US 1/1/2011 2011-01-01
fr 01/01/2011 2011-01-01
nl 1-1-2011 2011-01-01
no 01.01.2011 2011-01-01
ru 01.01.2011 2011-01-01

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

T-SQL
1
2
select locale,FORMAT ( 100, 'c', locale ) AS Result
from Locales
select locale,FORMAT ( 100, 'c', locale ) AS Result
from Locales

locale Result
de 100,00 €
en-US $100.00
fr 100,00 €
nl € 100,00
no kr 100,00
ru 100,00р.

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

T-SQL
1
2
3
4
5
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
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

locale Result1 Result2 Result3 Result4
de 100,3 € 100,34 € 100,340 € 100,3400 €
en-US $100.3 $100.34 $100.340 $100.3400
fr 100,3 € 100,34 € 100,340 € 100,3400 €
nl € 100,3 € 100,34 € 100,340 € 100,3400
no kr 100,3 kr 100,34 kr 100,340 kr 100,3400
ru 100,3р. 100,34р. 100,340р. 100,3400р.

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