Parsing DateTime on Localized Systems

Question

Hello,

We have a web application that produces reports. Data are taken from a database.

When we ran the web application on a localized system, it blows up. We traced the problem on a DateTime.Parse(dateString); call.

The dates stored in the database is somewhat dependent on the locale of the machine.

On an English system, the date is stored as MM/DD/YYYY (06/25/2009) which is perfectly normal.

On a Russian system, the date is stored as MM.DD.YYYY (06.25.2009). This is weird because the default setting (I checked) for Short Date format in Russian Systems is dd.MM.yyyyy... So it should be 25.06.2009. I don't it get why it accepted the default separator (.) but not the default date format.

So anyway, how can I parse the date string on a localized system? If I use the Russian cultureinfo, it would still throw an error since it is expecting dd.MM.yyyyy.

Thanks!

Answer 1

You should never store a localized date string in the database.

You must store dates in date columns and then localize the data at the moment of showing it.

1. Set the locale of your site UI

This example set the lang according to the request path, but may have other mechanism.

Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
    Dim lang As String
    If HttpContext.Current.Request.Path.Contains("/en/") Then
        lang = "en" 'english'
    ElseIf HttpContext.Current.Request.Path.Contains("/pt/") Then
        lang = "pt" 'portugues'
    Else
        lang = "es" 'español, the default for the site'
    End If
    Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo(lang)
    Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(lang)

End Sub

2. Get the input to date variables

Getting the data from the text input is easy, this is an example, but with a date validator you should be ok.

dim theDate as date
if IsDate( txtDate.text ) then
   theDate = DateTime.Parse(txtDate.text)
else
   'throw exception or something
end if

Edit

Since you say you can't change the way it is stored, you are in big trouble, unless you have in the db record some way to tell the format of the date. The problem is not the separators, but when you find a date like 6/3/2009 you dont know if it is 6 of march or 3 of june.

Answer 2

It depends on the RDBMS really. See if you can get the database to store dates in either the invariant culture or a specific (non-changing) culture you choose, then supply the proper CultureInfo to the DateTime.Parse() method. This doesn't seem to be an option anymore as you don't have control over storing the dates.

You'll have to see if you can figure out how it is storing the dates and act accordingly. Check the OS settings on the Russian system; maybe someone changed it from the defaults and the database is reacting accordingly? If so, you can probably react in the same manner.

Otherwise you'll need some correlation between the system and the way the dates are stored. If all Russian systems store it in the same fashion you can switch on the current culture and use ParseExact. Feels like a bit of a hack though, but I'm not sure you have another choice.

Answer 3

To be blunt, you need to:

• Know the expected format in the database. If at all possible, make this either a single standard format as a string, or preferably a date/time type in the database (which you can then read from the database as a DateTime, without your code performing any conversions).
• Use the expected format by calling DateTime.ParseExact and specifying the format string explicitly.

Answer 4

At the database, if you must store it as text, you should standardize the format. Even better, though, store it as datetime, and there is nothing to parse - just read it out from the database as a date...

If you have a string, use the known culture (perhaps the invariant culture) when parsing:

DateTime when = DateTime.Parse(s, CultureInfo.InvariantCulture);

Answer 5

It sounds like your problem is in the connection to your database.

On windows you should: - check the locale settings of your Data Source , - your connection string to the data, - or change the default language for the user on the RDBMS.

Each of these could ensure that your data is returned in the correct format.

The exact details of the solution depends of course on the database you are talking to and the OS you are using.