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
Expression of a supported data type to 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.“
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
|de||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|
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