How to get SQL Server 2012 to use the invariant culture in format()?

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)

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.

StackExchange Database Administrators Q#48289, answer score: 3