Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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


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)

share|improve this question
    
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
1  
Please report this on Connect and include a link to it here (I'll happily vote for it). For those wondering, the behaviour hasn't changed in the 2014 CTP. –  Jon Seigel Sep 15 '13 at 12:48
    
@JonSeigel Posted, edited in the link. –  GSerg Sep 15 '13 at 15:17
add comment

1 Answer

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.

share|improve this answer
    
Very interesting, thank you. –  GSerg Sep 15 '13 at 9:06
    
I wonder if this should be posted to connect.misrosoft.com now. –  GSerg Sep 15 '13 at 9:10
    
Apparently people found nchar(0) to be able to crash SQL Server. –  GSerg Nov 29 '13 at 16:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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