title: How to Convert date into dd.MM.yyyy format in C# from excel
body: <p>In excel sheet i have date in format <code>dd.MM.yyyy</code> 16.10.2011 <strong>(16 as dd, 10 as MM)</strong>. When i'm importing data from excel to SQL Server 2008 i get <code>MM.dd.yyyy</code> but still 16.10.2011 <strong>(16 as MM, 10 as dd)</strong>. That's not correct. I found solution on this: go to SQL Server 2008 -> Security -> logins -> {user properties} -> and change default language for user. With this solution i get in SQL Server 2008 <code>dd.MM.yyyy</code> 16.10.2011 <strong>(16 as dd, 10 as MM)</strong>. Is there any other way to convert date in <code>dd.MM.yyyy</code> format without changing user language?</p>
  
  <pre><code>String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\JantarV7Export.xlsx;Extended Properties=Excel 8.0";
  OleDbConnection olecon = new OleDbConnection(sConnectionString);
  
  olecon.Open();
  OleDbCommand cmd = olecon.CreateCommand();
  cmd.CommandText = "SELECT person_id, date_of_hours, number_of_hours FROM [Sheet1$]";                    
  OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
  OleDbDataReader odr = cmd.ExecuteReader();
  conn.Open();
  while (odr.Read())
  {
      SqlCommand cmd1 = conn.CreateCommand();
  
      cmd1.CommandText = "INSERT INTO test_data values (newid(),'" + odr[0] + "',@date_of_hours,'" + odr[2] + "')";
      cmd1.Parameters.Add("@date_of_hours", SqlDbType.DateTime).Value =odr[1];
  }
  cmd1.ExecuteNonQuery();
  olecon.Close();
  conn.Close();
  </code></pre>
  
  <p>I think that problem is when i'm inserting into <code>test_data</code> datatable. So i probably should convert dates somehow before inserting. What do you suggest? Thanks. Is there any options like this:</p>
  
  <pre><code>cmd1.CommandText = "INSERT INTO test_data values (newid(),'" + odr[0] + 
      "',CONVERT(VARCHAR(20),@date_of_hours,104),'" + odr[2] + "')";
  cmd1.Parameters.Add("@date_of_hours", SqlDbType.DateTime).Value =odr[1];
  </code></pre>
  
  <p>(this doesn't work because of CONVERT in insert, but is there some similar solution) or</p>
  
  <pre><code>CONVERT(VARCHAR(20),@date_of_hours,104) AS [DD:MM:YYYY]
  </code></pre>
  
  <p><strong>UPDATE</strong>
  In other form i read data through combobox. It shows 1.10.2011, 2.10.2011, .... to 16.10.2011.</p>
  
  <pre><code>da_test_data = new SqlDataAdapter("SELECT test_data_id, date_of_hours, number_of_hours FROM test_data WHERE _person_id= '" + person_id_person + "' ORDER BY date_of_hours", conn);
  dt_test_data = new DataTable();
  da_test_data.Fill(dt_test_data);
  cb_datum.DataSource = dt_test_data.DefaultView;
  cb_datum.ValueMember = "test_data_id";
  cb_datum.DisplayMember = "date_of_hours";
  </code></pre>
  
  <p>And combobox for <strong>projects</strong>:</p>
  
  <pre><code> private void cb_datum_SelectedIndexChanged(object sender, EventArgs e)
   {
        DataRowView drvProjekt = cb_datum.SelectedItem as DataRowView;
        if (drvProjekt != null)
        {
            string date1 = drvProjekt["date_of_hours"].ToString();  
            DateTime date2 = new DateTime();
            date2 =Convert.ToDateTime(date1);
  
            //DateTime date = DateTime.ParseExact(date1, "MMddyyyy hh:mm:ss", null);
            //DateTime date = DateTime.ParseExact(date1, "dd.MM.yyyy", System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat);
  
            //object obj = DateTime.ParseExact(dddd, "MM/dd/yyyy hh:mm", null);
            conn = new SqlConnection(connectionString);
            conn.Open();
  
            da_projekt = new SqlDataAdapter("SELECT projekt_id, name_of_projekt, date_of_start, date_of_end FROM projekt WHERE date_of_start &lt; '" + date2 + "' AND date_of_end &gt; '" + date2 + "' ", conn);
            dt_projekt = new DataTable();
            da_projekt.Fill(dt_projekt);
            cb_projekt_id.DataSource = dt_projekt.DefaultView;
            cb_projekt_id.ValueMember = "projekt_id";
            cb_projekt_id.DisplayMember = "name_of_projekt";
            conn.Close();
        }
   }
  </code></pre>
  
  <p>When i select 13.10.2011 i get error. Date is recognised as MM.dd.yyyy. I tried everything (all in comments and many more). Yust don't find a solution.           </p>