.NET Culture and SQL Server

Overview

Many times we need to convert data, such as a Date retrieved from a DBMS, into a string representation. The culture setting of our web browser, done either by our PC's configuration or by the web application itself, can incorrectly affect how the Date to String conversion occurs.

For this discussion we will be using SQL Server 2005 as the DBMS.

A SQL Server Instance has a default Language, which can be "seen" by running this snippet:

    SP_CONFIGURE 'default language'

The results will look something like this, where config_value indicates the langid.

default language


By running the following stored proc we can can see the languages supported by the SQL Server installation, per langid. In this case, config_value = langid = "us_english".


    SP_HELPLANGUAGE

default language


Each Server Login has its own Language setting (in SQL Server Management Studio, expand the Securities node, then the Logins node, highlight a login, right-click then Properties) which can be seen by running this script:

    SELECT @@LANGUAGE

The result will match an entry in the Name column in the results from SP_HELPLANGUAGE. For "us_english" we see that the date format is "mdy".


.NET, Locale, and Culture

From MSDN:

The .NET Framework uses the terminology "culture" to represent what might have traditionally been called the "locale". The .NET Framework has two concepts of the active culture.

Culture, which is indicated by the Thread.CurrentCulture property, corresponds by default to the selection in Regional Options in the control panel. This affects how numbers, dates, and times are formatted, and is also what determines which sorting and casing rules to use. This is the property we are concerned with here.

UICulture, which is indicated by the Thread.CurrentUICulture property, corresponds by default to the language of the operating system, or the selected language on a multi-language version of Microsoft® Windows®. This affects which resources get loaded, so it determines which strings and pictures the user sees. This is not the property we are concerned with in this case.


Using CDate() and CType()

For Date to String conversion using VB.NET CDate() for example, if the date format of my Thread.CurrentCulture is "dmy" when my SQL Server Login is "mdy", I will get undesired results. If I try to compare the converted result to the database, or perform an update to the database, incorrect results and even errors can occur.

Let's compare results between "en-US" and "en-GB" (United States vs. Great Britain) date conversions, when incorrectly done:

For my DBMS Login, STEVE\ASPNET, the Default Language is "English". As we saw above, the date format is "mdy". The following Winform code does a simple Date to String conversion:

   'simulate reading a date from the database
   Dim oDate = "12/13/2009"
   Dim dt As Date

   'ensure my culture setting is "en-US"
   Dim ciUS As New Globalization.CultureInfo("en-US")
   Thread.CurrentThread.CurrentCulture = ciUS

   'do unsafe conversion; I could also do dt = CDate(oDate)
   dt = (CType(oDate, Date))
   MsgBox(ciUS.EnglishName & " : " & dt.ToString)

The above code works because my database date format matches my CurrentCulture format.

However, if my database Language was "British" with date format "dmy", since my CurrentCulture in .NET is "en-US" the date conversion will fail.

   'simulate reading a date from the database as British "dmy" format
   Dim oDate = "13/12/2009"
   Dim dt As Date

   'ensure my culture setting is "en-US"
   Dim ciUS As New Globalization.CultureInfo("en-US")
   Thread.CurrentThread.CurrentCulture = ciUS

   'this will throw an error, since "13/12/2009" is
   'not a valid "en-US" date
   'CType and CDate use the CurrentThread.CurrentCulture
   'setting, "en-US"
   dt = (CType(oDate, Date))
   MsgBox(ciUS.EnglishName & " : " & dt.ToString)
 

Let's try the other way, where the database Language is "us_english" and my .NET application has a culture setting is "en-GB".

   'simulate reading a date from the database in US m/d/y format
   Dim oDate = "12/13/2009"
   Dim dt As Date

   'set culture setting to "en-GB"
   Dim ciGB As New Globalization.CultureInfo("en-GB")
   Thread.CurrentThread.CurrentCulture = ciGB

   'do unsafe conversion; I could also do dt = CDate(oDate)
   dt = (CType(oDate, Date))
   MsgBox(ciGB.EnglishName & " : " & dt.ToString)

This will try and convert a US date in "mdy" (which we have in an Object data type), namely December 13, 2009, to a Date data type. But since the CurrentCulture is British it will treat 12/13/2009 such that it will try and use the value "13" for the month, which of course is invalid and will throw an error.

Also, if I try and convert a String to a Date value so I can then save it to the database, I may have a problem if my CurrentCulture date format does not match my database Language date format.


Using System.IFormatProvider

The bottom line is that your application's Date to String conversion functions should use a date format matching the database Login Language's date format.

CDate() and CType() use the CurrentThread.CurrentCulture setting, with no awareness of the database Login language setting. So I need to explicitly set the CurrentThread.CurrentCulture to match the database Login's setting before I use these functions.

Alternatively, we can instead use System.IFormatProvider:

   Dim oDate = "12/13/2009"
   Dim dt As Date

   'ensure my culture setting is "en-US"
   Dim ciUS As New Globalization.CultureInfo("en-US")
   Thread.CurrentThread.CurrentCulture = ciUS

   'convert Object data type to Date data type
   dt = (CType(oDate, Date))

   'Now, convert Date to String, but in British formatting
   Dim ci As New Globalization.CultureInfo("en-GB")

   'force String conversion with "en-GB" formatting
   'and IFormatProvider
   Dim sDate as string = dt.ToString("d", ci.DateTimeFormat)
   MsgBox(sDate)

The IFormatProvider interface allows you to control formatting without having to change your Thread.CurrentThread.CurrentCulture.


Summary

When converting data such as date or numeric to a String, CDate() and CType() are not sufficient for conversion by themselves.

We need to know the Language setting of the SQL Server database Login being used, and make use of CurrentThread.CurrentCulture and leverage IFormatProvider where possible. There are a number of other places that IFormatProvider can be used, but that will have to wait for another article.

Some links:


Steve 1/1/2010 ("us_english")