Change Datatable Column Date Format

Question

I'm working with a txt files, I must insert that data in mysql database. So I get that info in a Datatable, then I can get that data in my gridview and I must insert that data in database. I can do that, but I have problems with date columns.

For example, When I try to insert, If I have this date in datagridview: 12/01/2018 no problem, but if I have 17/01/2018 not insert, I have manually changed and I've probed it.

this is read txt:

string LogAutoriz_fileName = Server.MapPath("~/Files/") + User.Identity.Name.ToString() + "_" + Path.GetFileName(fup_LogAutoriz_File.PostedFile.FileName);
    fup_LogAutoriz_File.SaveAs(LogAutoriz_fileName);

    DataTable dt = new DataTable();

    List<string[]> list = new List<string[]>();
    int maxItem = 0;
    using (System.IO.TextReader tr = File.OpenText(LogAutoriz_fileName))
    {
        string line;
        while ((line = tr.ReadLine()) != null)
        {
            string[] items = line.Trim().Split('    ');
            if (maxItem <= items.Count())
            {
                maxItem = items.Count();
            }
            list.Add(items);
        }


        //Crear las columnas del DataTable de Datos
        dt.Columns.Add("Comprobante", typeof(string));
        dt.Columns.Add("Serie_Comprobante", typeof(string));
        dt.Columns.Add("Ruc_Emisor", typeof(string));
        dt.Columns.Add("RazonSoc_Emisor", typeof(string));
        dt.Columns.Add("Fecha_Emision", typeof(string));
        dt.Columns.Add("Fecha_Autoriz", typeof(string));
        dt.Columns.Add("Tipo_Emision", typeof(string));
        dt.Columns.Add("Ident_Receptor", typeof(string));
        dt.Columns.Add("Basura", typeof(string));
        dt.Columns.Add("Clave_Acceso", typeof(string));
        dt.Columns.Add("Numero_Autor", typeof(string));
        dt.Columns.Add("Importe_Total", typeof(string));



        foreach (var items in list)
        {
            dt.Rows.Add(items);
        }


        //cargar al grid
        this.gvwAutorizaciones.DataSource = dt;
        this.gvwAutorizaciones.DataBind();

And this is for insert every gridview record in data base:

try
{
    using (MySqlConnection sqlCon = new MySqlConnection(conn))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            cmd.CommandText = "Insert into autorizaciones " +
                "(comprobante, Serie_Comprobante, Ruc_Emisor, RazonSoc_Emisor, " +
                "Fecha_Emision, Fecha_Autorizacion, " +
                "Tipo_Emision, Ident_Receptor, Clave_Acceso, Numero_Autorizacion) " +
                "values(" +
                "'" + comprob + "', " +
                "'" + serComprob + "', " +
                "'" + rucEmisor + "', " +
                "'" + razSocEmi + "', " +
                "'" + FecEmisor + "', " +
                "'" + FecAutoriz + "', " +
                "'" + tipoEmision + "', " +
                "'" + identRecep + "', " +
                "'" + claveAcceso + "', " +
                "'" + numAutoriz + "')";
            cmd.Connection = sqlCon;
            sqlCon.Open();
            cmd.ExecuteNonQuery();
            sqlCon.Close();
        }
    }
}

catch (MySqlException ex)
{
}

I call this in a foreach and I can insert records, but for dates, I have that problem.

Is there any way to format the column datatable, for example "yyyy-mm-dd"?

If please anyone can help me,

thanks a lot best regards

**WARNING**: This code is riddled with [SQL injection bugs](http://bobby-tables.com/) because the SQL statements inline raw content without proper escaping. Consider using *prepared statements with placeholder values* whenever possible. — tadman, Feb 28 '18 at 22:56
which column is your datetime field in table? in which format is it? — Rojalin Sahoo, Mar 01 '18 at 06:12
DateTime variables do not have a format. Only when you use it somewhere or call ToString are they represented in a specific format. If you want to convert string to DateTime you have to make sure they are in the right format, and for that there are tons of exaples here on SO alone. — VDWWD, Mar 01 '18 at 08:03
Possible duplicate of [Converting a String to DateTime](https://stackoverflow.com/questions/919244/converting-a-string-to-datetime) — VDWWD, Mar 01 '18 at 08:07

score 4 · Accepted Answer · edited Mar 01 '18 at 06:27

I suppose, you should change the type of your column because of it is a type of string.

DateTime newDate= DateTime.Parse(stringDate);---> You can use this to convert string to date,

or

string s = "13/5/2014 12:00:00 AM";    
var date = DateTime.ParseExact(s, "dd/M/yyyy hh:mm:ss tt",
                                   CultureInfo.InvariantCulture);

You can use above code block to change the format of the date time.

Also you can determine your data table column type before you create it like,

DataColumn colDateTime = new DataColumn("DateTimeCol");   
colDateTime.DataType = System.Type.GetType("System.DateTime");

All I mean, I think you have type problem so I hope this helps.

Thanks a lot Rojalin, works fine with a correct date format: var FechaAutorizacion = DateTime.ParseExact( grd_Row.Cells[5].Text.Remove(10,9), "dd/M/yyyy", CultureInfo.InvariantCulture); — angel_neo, Mar 01 '18 at 17:56

score 0 · Answer 2 · answered Mar 01 '18 at 07:34

12/01/2018 no problem, but if I have 17/01/2018 not insert

Your actual value is in DD/MM/YYYY but insert mysql taking the format as MM/DD/YYYY. So you can format that particular column value to MM/DD/YYYY. I am considering identRecep is a Datetime field.

try
 {
   using (MySqlConnection sqlCon = new MySqlConnection(conn))
     {
       using (MySqlCommand cmd = new MySqlCommand())
        {
          cmd.CommandText = "Insert into autorizaciones " +
          "(comprobante, Serie_Comprobante, Ruc_Emisor, RazonSoc_Emisor, " +
          "Fecha_Emision, Fecha_Autorizacion, " +
          "Tipo_Emision, Ident_Receptor, Clave_Acceso, Numero_Autorizacion) " + 
 "values(@comprob,@serComprob,@rucEmisor,@razSocEmi,@FecEmisor,@FecAutoriz,@tipoEmision,@identRecep,@claveAcceso,@numAutoriz)";
     cmd.Parameters.AddWithValue("@comprob", comprob);
     cmd.Parameters.AddWithValue("@serComprob", serComprob);
     cmd.Parameters.AddWithValue("@razSocEmi", razSocEmi);
     cmd.Parameters.AddWithValue("@FecEmisor", FecEmisor);
     cmd.Parameters.AddWithValue("@FecAutoriz", FecAutoriz);
     cmd.Parameters.AddWithValue("@tipoEmision", tipoEmision);
     cmd.Parameters.AddWithValue("@identRecep", DateTime.Parse(identRecep,"MM/DD/YYYY"));
     cmd.Parameters.AddWithValue("@claveAcceso", claveAcceso);
     cmd.Parameters.AddWithValue("@numAutoriz", numAutoriz);
     cmd.Connection = sqlCon;
     sqlCon.Open();
     cmd.ExecuteNonQuery();
     sqlCon.Close();
  }
 }
}
catch (MySqlException ex)
    {
    }

score 0 · Answer 3 · answered Mar 01 '18 at 17:59

Thanks a lot Rojalin, I changed this part of my code:

private void RegistrarAutorizacion()
{
    try
    {
        foreach (GridViewRow grd_Row in this.gvwAutorizaciones.Rows)
        {
            string Comprobante = Convert.ToString(grd_Row.Cells[0].Text.Replace("&nbsp;", ""));
            string SerieComprobante = Convert.ToString(grd_Row.Cells[1].Text.Replace("&nbsp;", ""));
            string RucEmisor = Convert.ToString(grd_Row.Cells[2].Text.Replace("&nbsp;", ""));
            string RazSocEmisor = Convert.ToString(grd_Row.Cells[3].Text.Replace("&nbsp;", ""));
            //DateTime FechaEmision = Convert.ToDateTime(grd_Row.Cells[4].Text.Replace("&nbsp;", ""));
            //DateTime FechaAutorizacion = Convert.ToDateTime(grd_Row.Cells[5].Text.Replace("&nbsp;", ""));
            var FechaEmision = DateTime.ParseExact(
                grd_Row.Cells[4].Text.Replace("&nbsp;", ""), 
                "dd/M/yyyy", CultureInfo.InvariantCulture); 
            var FechaAutorizacion = DateTime.ParseExact(
                grd_Row.Cells[5].Text.Remove(10,9),
                "dd/M/yyyy", CultureInfo.InvariantCulture); //file text has time, but for my process is not important the hour


            string TipoEmision = Convert.ToString(grd_Row.Cells[6].Text.Replace("&nbsp;", ""));
            string IdentidadRecepetor = Convert.ToString(grd_Row.Cells[7].Text.Replace("&nbsp;", ""));
            string ClaveAcceso = Convert.ToString(grd_Row.Cells[9].Text.Replace("&nbsp;", ""));
            string NumeroAutorizacion = Convert.ToString(grd_Row.Cells[10].Text.Replace("&nbsp;", ""));


            autorizDL.RegistrarAutorizacion(
                Comprobante, SerieComprobante, RucEmisor, RazSocEmisor,
                Convert.ToDateTime(FechaEmision),
                Convert.ToDateTime(FechaAutorizacion),
                TipoEmision, IdentidadRecepetor, ClaveAcceso, NumeroAutorizacion);

        }
    }
    catch (System.FormatException sfex) { }
    //catch (Exception ex) { }

}

and I can insert new database entries. thank you again.

best regards

Change Datatable Column Date Format

3 Answers3