It can be easy to forget that the T-SQL FORMAT() function provides locale-aware formatting. Locale-aware means that the locale can affect the results. In other words, the exact output you get will depend on the locale.
By default, the function uses the language of the current session to determine the locale. However, this can be overridden by passing a “culture” argument to the function. Doing this allows you to provide results for a particular locale without having to change the language of the current session.
This article contains examples of how locale can affect the results when using the FORMAT() function in SQL Server.
Example 1 – Currencies
Here’s a quick example to demonstrate how language/culture can affect your results when formatting numbers.
DECLARE @num decimal(6,2) = 1234.56; SELECT FORMAT(@num, 'C', 'en-us') 'en-us', FORMAT(@num, 'C', 'en-gb') 'en-gb', FORMAT(@num, 'C', 'th-th') 'th-th', FORMAT(@num, 'C', 'nl-nl') 'nl-nl', FORMAT(@num, 'C', 'ne-np') 'ne-np', FORMAT(@num, 'C', 'fa-ir') 'fa-ir';
Results:
+-----------+-----------+-----------+------------+------------+--------------+ | en-us | en-gb | th-th | nl-nl | ne-np | fa-ir | |-----------+-----------+-----------+------------+------------+--------------| | $1,234.56 | £1,234.56 | ฿1,234.56 | € 1.234,56 | रु 1,234.56 | 1,234/56ريال | +-----------+-----------+-----------+------------+------------+--------------+
The C in this example is a standard numeric format specifier. This single character specifies that the value should be formatted in a certain way (in this case, as a currency). Fortunately, SQL Server is smart enough to know that not all cultures use the same format, and it automatically presents a different format depending on the culture.
In the above example, each time I call FORMAT(), I pass the same value and format string. The only difference is the value of the culture argument. This causes the results to be different, depending on the culture being used. The currency symbol and its positioning is determined by the culture. The character used for decimal separators and group separators is also determined by the culture.
Example 2 – Negative Values
Formatting can also depend on whether the value is positive or negative. If we use a negative value, here’s what happens:
DECLARE @num decimal(3,2) = -1.23; SELECT FORMAT(@num, 'C', 'en-us') 'en-us', FORMAT(@num, 'C', 'en-gb') 'en-gb', FORMAT(@num, 'C', 'th-th') 'th-th', FORMAT(@num, 'C', 'nl-nl') 'nl-nl', FORMAT(@num, 'C', 'ne-np') 'ne-np', FORMAT(@num, 'C', 'fa-ir') 'fa-ir';
Results:
+---------+---------+---------+---------+---------+-----------+ | en-us | en-gb | th-th | nl-nl | ne-np | fa-ir | |---------+---------+---------+---------+---------+-----------| | ($1.23) | -£1.23 | -฿1.23 | € -1,23 | -रु 1.23 | 1/23-ريال | +---------+---------+---------+---------+---------+-----------+
In some cultures, the minus sign appears before the currency sign, in others it appears after it. But in other cultures, there’s no minus sign at all – it’s replaced with parentheses that surround the whole result, including the currency sign.
However, we shouldn’t assume that the same rules are applied across all format strings. For example, if we format it as a number instead of a currency, we don’t get any parentheses:
DECLARE @num decimal(3,2) = -1.23; SELECT FORMAT(@num, 'N', 'en-us') 'en-us', FORMAT(@num, 'N', 'en-gb') 'en-gb', FORMAT(@num, 'N', 'th-th') 'th-th', FORMAT(@num, 'N', 'nl-nl') 'nl-nl', FORMAT(@num, 'N', 'ne-np') 'ne-np', FORMAT(@num, 'N', 'fa-ir') 'fa-ir';
Results:
+---------+---------+---------+---------+---------+---------+ | en-us | en-gb | th-th | nl-nl | ne-np | fa-ir | |---------+---------+---------+---------+---------+---------| | -1.23 | -1.23 | -1.23 | -1,23 | -1.23 | 1/23- | +---------+---------+---------+---------+---------+---------+
Example 3 – Dates and Times
Number formatting isn’t the only thing that’s affected by culture. Dates and times for example, will also be formatted differently depending on the culture.
DECLARE @date datetime2(0) = '2019-06-15 13:45:30'; SELECT FORMAT(@date, 'G', 'en-us') 'en-us', FORMAT(@date, 'G', 'en-gb') 'en-gb', FORMAT(@date, 'G', 'th-th') 'th-th', FORMAT(@date, 'G', 'nl-nl') 'nl-nl', FORMAT(@date, 'G', 'ne-np') 'ne-np', FORMAT(@date, 'G', 'fa-ir') 'fa-ir';
Results (using vertical output):
en-us | 6/15/2019 1:45:30 PM en-gb | 15/06/2019 13:45:30 th-th | 15/6/2562 13:45:30 nl-nl | 15-6-2019 13:45:30 ne-np | 6/15/2019 1:45:30 अपराह्न fa-ir | 25/03/1398 01:45:30 ب.ظ
This example uses a general date/long time format (achieved by using G – one of the standard date and time format specifiers), and the differences between cultures is obvious.
But we can also see differences even when using a long date format:
DECLARE @date datetime2(0) = '2019-06-15 13:45:30'; SELECT FORMAT(@date, 'F', 'en-us') 'en-us', FORMAT(@date, 'F', 'en-gb') 'en-gb', FORMAT(@date, 'F', 'th-th') 'th-th', FORMAT(@date, 'F', 'nl-nl') 'nl-nl', FORMAT(@date, 'F', 'ne-np') 'ne-np', FORMAT(@date, 'F', 'fa-ir') 'fa-ir';
Results (using vertical output):
en-us | Saturday, June 15, 2019 1:45:30 PM en-gb | 15 June 2019 13:45:30 th-th | 15 มิถุนายน 2562 13:45:30 nl-nl | zaterdag 15 juni 2019 13:45:30 ne-np | शनिवार, जून 15, 2019 1:45:30 अपराह्न fa-ir | شنبه, 25 خرداد 1398 01:45:30 ب.ظ
Example 4 – What about Custom Format Strings?
The previous examples use standard format strings, which pretty much do the formatting for you. It’s like a shorthand way of specifying a custom format string. A custom format specifier on the other hand, allows you to specify exactly what characters appear in the output, and where they go. However, this typically means that you need to use more format specifiers in your format string.
But even when you use custom format specifiers, the exact output can also depend on the locale. If we wanted to use a custom date and time format string to mimic the previous example, we might do something like this:
DECLARE @date datetime2(0) = '2019-06-15 13:45:30'; SELECT FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'en-us') 'en-us', FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'en-gb') 'en-gb', FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'th-th') 'th-th', FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'nl-nl') 'nl-nl', FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'ne-np') 'ne-np', FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm:ss tt', 'fa-ir') 'fa-ir';
Results (using vertical output):
en-us | Saturday, 15 June 2019 01:45:30 PM en-gb | Saturday, 15 June 2019 01:45:30 PM th-th | เสาร์, 15 มิถุนายน 2562 01:45:30 PM nl-nl | zaterdag, 15 juni 2019 01:45:30 ne-np | शनिवार, 15 जून 2019 01:45:30 अपराह्न fa-ir | شنبه, 25 خرداد 1398 01:45:30 ب.ظ
Probably the most obvious observation is that the result is formatted using the language of the specified locale. But if we look closely, we can see that it also ignores the AM/PM designator (tt) for the nl-nl culture, probably because that culture typically uses the 24 hour clock. We can also see that even our positioning can be ignored in some cases (e.g. fa-ir).
However, not everything is ignored and so we end up with a combination of our explicit specifications and those determined by the locale.
Finding/Changing your Current Language
As mentioned, if you don’t provide the “culture” argument, the language of your current session will be used to determine the locale.
There are several ways to find the language of your current session.
You can also change the locale of your current connection.
Alternatively, you can just use the SET LANGUAGE statement to switch the current language as required.
Here’s a quick example using SET LANGUAGE to show that your own language settings can affect the formatting results just the same as when you use the “culture” argument, like in the previous examples.
DECLARE @num decimal(3,2) = -1.23; SET LANGUAGE British; SELECT FORMAT(@num, 'C') Result; SET LANGUAGE US_English; SELECT FORMAT(@num, 'C') Result;
Results:
+----------+ | Result | |----------| | -£1.23 | +----------+ +----------+ | Result | |----------| | ($1.23) | +----------+