5

This has now been posted to Connect: The invariant culture identifier is rejected by the FORMAT() function in SQL Server 2012. (Connect has been retired.)
This has now been posted to feedback.azure.com.


I'm trying to get the built-in format() function in SQL Server 2012 to use the invariant culture.

It is said in the documentation that the function accepts a .NET culture identifier as the third parameter. The identifier for the invariant culture is a blank string:

You specify the invariant culture by name by using an empty string ("") in the call to a CultureInfo instantiation method.

That does not work with SQL Server however:

select format(getdate(), N'g', '');

Msg 9818, Level 16, State 1, Line 1
The culture parameter '' provided in the function call is not supported.

It is also documented that the invariant culture is associated with the English language, but not with any country/region. One would think this allows to pass 'en' as the identifier, but then, in .NET, CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("")) yields true, but CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("en")) gives false, so they aren't really the same.

So how do I make SQL Server to use the invariant culture?

(Note: I'm interested in making the built-in thing to work. I already have my own CLR functions to do this, I was going to remove them in favor of the now-built-in functionality).

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)

  • What happens when you leave out the third parameter? SELECT FORMAT(getdate(), N'g'); Also why do you need SQL Server to produce a datetime using invariant culture? SQL Server can produce a datetime value without dealing with any of this format or CLR stuff, and the receiving application can handle it however it wants. You shouldn't care about formatting a date until long after it's left SQL Server, IMHO, and even then you should probably format it in a standard, unambiguous way (e.g. yyyy-mm-dd) instead of whatever the invariant produces on a particular machine. – Aaron Bertrand Aug 17 '13 at 18:45
  • @AaronBertrand Omitting the third parameter, as documented, gives you the result formatted according to your connection language (SET LANGUAGE). Invariant culture is supposed to be invariant accross machines, which makes it perfect for serialization. So it's used on the SQL Server level when it comes to serializing data in some form or another, this includes generating an XML off a query. The 'g' was just an example, I'm not actually using that. And I can emulate the invariantness by escaping the special characters in the format string ('dd\/MM\/yyyy'), but that's not pretty. – GSerg Aug 17 '13 at 19:21
3

I have found only one way to get FORMAT to use the invariant culture: pass a string starting with the null character for the culture parameter.

-- Setting session language to Traditional Chinese to demonstrate that this
-- isn't just falling back to the session language.
SET LANGUAGE N'Traditional Chinese';
PRINT N'Session Language:  ' + FORMAT(SYSDATETIME(), N'Y');          
PRINT N'US English:        ' + FORMAT(SYSDATETIME(), N'Y', N'en-US');
PRINT N'Invariant Culture: ' + FORMAT(SYSDATETIME(), N'Y', NCHAR(0));


Output:

Session Language:  2013年9月
US English:        September, 2013
Invariant Culture: 2013 September

(The above example is using the "year month" format string of 'Y' since it produces different output between en-US and the invariant culture.)

This works due to what appears to be a quirk of how the .NET implementation of CultureInfo looks up the culture data. The string comparisons it is doing seem to ignore any data following a null character, likely because they are being done within native code in the CLR via functions intended for use with null-terminated strings. It therefore sees a string starting with a null character as being equivalent to the empty string, which as you noted is the documented identifier for the invariant culture.

CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("\0foo")) //returns true


DISCLAIMER: This is obviously rather hackish since this behavior is not documented for FORMAT nor for CultureInfo. I have only tried this with SQL Server 2012 running with .NET 4.0.3. I personally would not rely on this behavior for anything that cannot be easily changed if it were to stop working due to a future update.

  • Apparently people found nchar(0) to be able to crash SQL Server. – GSerg Nov 29 '13 at 16:23
  • @GSerg and Kevin: I actually can't get NCHAR(0) to work with the built-in FORMAT command. I suspect that this stopped working when the 'iv' value started working. Please see my answer for testing details. – Solomon Rutzky May 11 '18 at 6:32
2

Use 'iv'. Works on 2017, and should run on any version, because it's connected with .NET not with SQL Server itself.

If the culture argument is not provided, 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.

PRINT N'Invariant Culture: ' + FORMAT(SYSDATETIME(), N'Y', 'iv');

Invariant Culture: 2018 May

UPDATE: Works only on 2017.

  • Yes, it also works on 2016. I wonder if this is completely undocumented though, can you provide any reference? The iv has to be a special case that comes from SQL Server, because .NET outside of SQL Server does not accept it as a valid culture identifier. – GSerg May 9 '18 at 15:51
  • I found it while inspecting CultureInfo class msdn.microsoft.com/en-us/library/… (see Remarks). I wonder why it does't work on my instance of 2016 (that's why I edited my answer today). – user3792838 May 10 '18 at 9:05
  • I have 2016 SP1 CU6, it works there. Install the cumulative updates? – GSerg May 10 '18 at 11:52
  • 1
    @GSerg and user3792838: my testing shows that 'iv' does work across SQL Server 2012, 2014, 2016, and 2017. I believe the overriding factor is the .NET Framework version on system, and not so much the version, or even patch level, of SQL Server (or at least it shouldn't be). Please see my answer for details. – Solomon Rutzky May 11 '18 at 6:29
  • Version: Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) Error: Msg 9818, Level 16, State 1, Line 2 The culture parameter 'iv' provided in the function call is not supported. .NET: .NET Framework 4.6.1 – user3792838 May 11 '18 at 9:31
1

I tested the approaches noted in both Kevin's answer and user3792838's answer, using both the built-in T-SQL FORMAT function as well as the equivalent .NET / SQLCLR function (using the SQL# library, which I wrote; and the Date_Format function is available in the Free version). I tested across 4 versions of SQL Server ( using SQL Server LocalDB, though that shouldn't matter ):

  • 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
  • 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64)
  • 2016 (SP1-GDR) (KB4057118) - 13.0.4210.6 (X64)
  • 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64)

I found that each test worked the same across all 4 version of SQL Server: one variation did not work in any version, and the other 3 worked in all 4 versions.

The first test, using the built-in FORMAT function and passing in NCHAR(0), failed each time (even though Kevin reported that it worked in SQL Server 2012 with .NET 4.0.3 on his system):

PRINT N'Invariant Culture (via FORMAT + NCHAR(0)): '
           + FORMAT(SYSDATETIME(), N'Y', NCHAR(0));

That received the following error:

Msg 9818, Level 16, State 1, Line XXXXX
The culture parameter '.' provided in the function call is not supported.

The next test, using the built-in FORMAT function and passing in 'iv' for the culture, worked every time (even though user3792838 stated that it only worked on SQL Server 2017 on their system):

PRINT N'Invariant Culture (via FORMAT + ''iv''): '
           + FORMAT(SYSDATETIME(), N'Y', 'iv');

The next two tests, using an equivalent .NET / SQLCLR method and passing in either NCHAR(0) or 'iv', worked every time:

PRINT N'Invariant Culture (via SQLCLR + NCHAR(0)): '
           + [SQL#].[Date_Format](SYSDATETIME(), N'Y', NCHAR(0));

PRINT N'Invariant Culture (via SQLCLR + ''iv''): '
           + [SQL#].[Date_Format](SYSDATETIME(), N'Y', 'iv');

In all working cases, the output was:

2018 May

This change in behavior appears to have been introduced in .NET 4.5 as it is noted in the documentation starting with that version (in the "Remarks" section). That info is not present in the .NET 4.0 documentation.

My system has the following .NET Framework version installed:

  • 4.5
  • 4.5.1
  • 4.5.2
  • 4.6
  • 4.6.1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.