Regional problems with C# and SQL Database

Question

Why would dateTimePicker.Value.Date.ToShortDateString(); act differently on Windows 7 x64 PL, Windows Vista x32 PL and Windows XP PL with to my knowledge exact regional settings. I've found it out the hard way that i was doing this conversion prior to entering it to DB.

It was working fine on Windows 7 (my development machine), colleague VISTA system but it failed to work on Windows XP (day was switched with month all the time). Also on higher systems we have 2010-01-13 displayed on ListView while on his system he has 13-01-2010.

I imagine in my old code i may have more of those type conversions and i will have to go thru and verify it but i would like to know why it's behaving that way on same regional settings. I imagine I should never do conversions like that but I've learn it the hard way after a long time when it was working fine.

EDIT:

I was using it this way (commented out code that was causing troubles). Back in the old days I thought ToShortDateString was the only way to make sure to put it into DB without Time (since i was reading DateTimePicker). I know now that I should have used .Date on that DateTimePicker but I am smarter now that it did blow up on me. Here's the code:

    private static void sqlWpiszDaneSwieta(DateTime varData, string varDataNazwa) {
        //string varDataSwieto = varData.ToShortDateString();
        const string preparedCommand = @"INSERT INTO [dbo].[TypyDatySwiat]
                                           ([SwietaData]
                                           ,[SwietaNazwa])
                                     VALUES
                                           (@varData
                                           ,@varDataNazwa)";
          using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (SqlCommand sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
            sqlWrite.Prepare();
            sqlWrite.Parameters.AddWithValue("@varData", varData);
            sqlWrite.Parameters.AddWithValue("@varDataNazwa", varDataNazwa);
            try {
                sqlWrite.ExecuteNonQuery();
            } catch (SqlException sqlEx) {
                if (sqlEx.Message.Contains("Violation of PRIMARY KEY constraint")) {
                    MessageBox.Show("Dodanie podanego święta jest niemożliwe. Podane święto istnieje już w bazie danych!", "Bład", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } else {
                    MessageBox.Show(sqlEx.ToString(), "Bład SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            } catch (Exception ex) {
                MessageBox.Show(ex.ToString(), "Bład", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

So i am not specifically asking for a way to do it. I know how to do it and that i can pass it using DateTime directly to db. Just that i would like to know why would it behave differently on 1 machine.

Answer 1

day was switched with month all the time

That sounds suspiciously like your colleague is using a UK locale on his XP machine, as that is the normal method for a date there.

But I'm more concerned about this:

prior to entering it to DB.

If you're putting it into a DB, why are you calling .ToShortDateString()? That smells like dynamic sql to me, and that means an sql injection vulnerability. Instead of something like this:

string sql = "INSERT INTO [MyTable] (MyDateColumn) VALUES (" + MyDateVar.ToShortDateString() + ")";
//sql command/connection code omitted from this sample

you need to do something like this:

string sql = "INSERT INTO [MyTable] (MyDateColumn) VALUES (@MyDate)"
using (var cn = new SqlConnection("..connection string.."))
using (var cmd = new SqlCommand(sql, cn))
{
    cmd.Parameters.Add("@MyDate", SqlDbType.DateTime).Value = MydateVar;

//remain code omitted

Notice the latter sample never converts the datetime variable to a string. It will work no matter what locale your users have set.

Answer 2

Sounds very much like regional settings. I would check the regional settings of the accounts that the applications are running with to see if this offers an explanation.

As for dealing with the problem, the ToBinary method on DateTime offers a safe way of exporting the value in such a way that it can be later recreated. Better still, considering this is going into a database, would be to pass in the value to the SQLCommand, as others have suggested.

Answer 3

I would have thought it would be better to store things like date/times in a database using the Invariant Culture. It's when you get it out of the database to display it to a user that you should be concerned about regional settings.

Rather than storing a ShortDateString in the database, you should use DateTime.ToString(System.Globalization.CultureInfo.InvariantCulture).

To see (in the code) what Culture you're using, just use System.Globalization.CultureInfo.CurrentCulture.Name.