DateTime.. out_of_range

[c-code-it]

Hi,
I have the following problem:
I’m developing with Visual Studio 2008, C# in an English environment.
I’m connecting to MS SQL Server 2008 and using the server Management Studio also in English.
I am working on a joint project which has been written with the same software but in a German environment!
The problem I have is when accessing the database I get the following error:

‘The conversion of a varchar data to a datetime data type results in an out-of-range value’

This does work within the German environment.

The problem is with the Dateformat when writing to the database. It can be made to work by adding the following statement at the start of the INSERT query: ‘set dateformat dmy;’.

Visual Studio uses the following dateTime definition:
‘public DateTime(int year, int month, int day, int hour, int minute, int second);’

My question is, is there a setting either in Visual studio or on the database that deals with the dateformats so that I do not have to change the code? If I change the code so it will run on my machine will it run on the other (German) machine?

Thankyou

c-code_it

[sknake]

You should use parameterized SQL and this won't be an issue any more. This is one reason dealing with DateTimes as string is a bad idea :)

Here is an example:

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    private void simpleButton1_Click(object sender, EventArgs e)
    {
      const string query = "Insert Into Employees (RepNumber, HireDate) Values (@RepNumber, @HireDate)";

      string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");
      DataTable dt;
      try
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@RepNumber", 50));
            cmd.Parameters.Add(new SqlParameter("@HireDate", DateTime.Today));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt = new DataTable();
              dt.Load(dr);
            }
          }
        }
        System.Diagnostics.Debugger.Break(); //At this point you have the populated datatable
      }
      catch (SqlException)
      {
        System.Diagnostics.Debugger.Break();
      }
    }

Notice that the "HireDate" field is a DateTime.

[kvprajapati]

Maybe scott's suggestion is solution of your problem but I suggest Globalization.

[c-code-it]

Thank's very much, will now start to parameteriz the code.

[sknake]

You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!