XML FeedsSQL 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
| 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
- 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
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.