SQL Compact DateTime Weirdness

Question

Hello everybody ::- ). I'm having a weird issue concerning Microsoft SQL Compact Edition. In a Windows.Forms application, I try to modify a database which was created by the same (.Net 2.0) application. The database gets sent to a Windows Mobile Phone later, but this is not important. What's important is that on the normal regional settings of my computer, which is English (USA), inserting DateTime values in the database happens without any problems. However, when I switch the locale to Dutch (Netherlands), I get the following error:

"There was an error in a part of the date format. [ Expression (if known) = ]"

I tracked it down to the way DateTime looks in NL. However, I do not use DateTime.ToString(). I add it to the SQL insert/update statements purely as "column = " + DateTime. This works perfectly in "en-US" but when I switch to Dutch, it blows up.

The way I fixed this is by creating an extension method for the DateTime datatype like so:

/// <summary>
/// Transforms a DateTime from various cultures into what SQL compact expects to get.
/// </summary>
/// <param name="original">DateTime to process.</param>
/// <returns>A SQL-friendly string.</returns>
public static string _ToSQLDateTimeString (this DateTime? original)
{ //No provided Date? Bye.
  if (original == null) return null;
  IFormatProvider usDate = new CultureInfo("en-US");
  return ((DateTime)original).ToString(usDate);
}

But I would like somebody to maybe confirm / improve my solution. Maybe I missed something?

Answer 1

If you want to push the DATETIME into the database a string, you're best of using the 2010-08-25T07:26:05 format - however, better would be to create a command object with parameters of the right types and then set the DATETIME parameter to the DateTime object rather than stringifying it.

SQL Server won't convert abritrary date string formats - it'll use the format dictated by whichever locale it's running under (default is US).

Answer 2

But "column = " + DateTime implicitly calls ToString() on the DateTime.

You should use System.Data.SqlClient.SqlCommand and add the dates (and all other parameters) with command.Parameters.Add(...).

This will fix all localization issues with dates, floats, etc. And protect your application from SQL injection attacks.