There's been an interesting discussion over on Microsoft.Public.SqlServer.Programming.CLR around the implementation of a CLR-based Date data type. I'm not going to get into if this is a particularly good idea or not (there's already enough contention over that, no need to poke nose into that again), but it did get me started on a particular train of thought. As you might know, SQL Server allows you to change the "langauge" use for query results. For example, consider:
set language 'english'
select convert(nvarchar(255),GetDate(),106)
set language 'japanese'
select convert(nvarchar(255),GetDate(),106)
If you're a .NET developer, you're probably ready to jump in and say "hey, that's not really language specific, that's Culture specific." Well, I suppose that's partially true -- if there were commonly used variants of Japanese. But the idea is right: what we're looking at here in T-SQL is similar in concept to what we'd do with the concept of Culture in .NET in that both provide ways to alter how dates, times, numbers and so are formated.
But here's the problem -- there's a couple of ways to figure out what culture/language is being used by SQL Server at query execution time. The first is @@LANGUAGE which returns the name of the current langauge setting and @@LANGID which provides an number for the current language. But neither of these values return anything especially useful to us as .NET developers. It'd be nice if @@LANGUAGE returned and IETF-style name like "ja-JP" instead of Japanese. We could then pass this into the constructor of a CultureInfo instance, for example, if we wanted to write a function that does some culture-specific formatting beyond what the T-SQL CONVERT styles support. It would be even easier if @@LANGID returned an LCID. But of course, they don't.
But... (and there's always a but, right?) But... there's a fairly smooth way of converting either @@LANGUAGE or @@LANGID into that magic LCID without writing our own lookup function. Here's the magic statement:
select lcid from sys.syslanguages where langid=@@langid
That query returns an LCID for the session's currently configured language. Yep, that's right, the compatibility view SYS.SYSLANGUAGES handily has a mapping for us. Sweet!
So, back to our example, it turns out that its fairly hard to get SQL Server to spit out a "年" for year, "月" or "日" or day just using CONVERT when formatting a date for Japanese. However, its pretty easy to do that with .NET's DateTime ToString method overload that accepts an instance of CultureInfo. Here's some example code for a simple function that shows that.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
public partial class DM.Examples.FunWithSysLanguages
{
[Microsoft.SqlServer.Server.SqlFunction
(DataAccess=DataAccessKind.None,IsDeterministic=true,IsPrecise=false,SystemDataAccess=SystemDataAccessKind.Read)]
public static SqlString CultureDate(SqlDateTime dt)
{
SqlConnection conn = new SqlConnection("context connection=true;");
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select lcid from sys.syslanguages where langid=@@langid";
cmd.CommandType = CommandType.Text;
using (conn)
{
conn.Open();
int lcid = (int)(cmd.ExecuteScalar());
CultureInfo ci = new CultureInfo(lcid);
return new SqlString(dt.Value.ToString(@"D",ci));
}
}
};
You might be asking, though, what got me started thinking about this. Well, it turns out that one my first thought abouts about this problem didn't work. I was hoping that when you executed a SET LANGUAGE command in T-SQL, doesn't seem to set or change .NET's CurrentThread.CurrentCulture or .CurrentUICulture. If it should or not seems like another whole debate like I didn't want to get into before. Let's just leave it at "if you find another way to get the @@LANGID within SQLCLR, please let me know." Otherwise, I think this solution is Okay. Obviously, it would be better if we could somehow cache and save the @@LANGID to LCID map (or get at it without a callout) so we're not wasting cycles getting what essentially boils down to constants.
That's an excercise I'll leave to you... :)