How to change date format for the date column in datatable?

25,546

Well, you didn't tell us how you create your dataTable but here my thought..

If your both DATE and TIME columns are DateTime, then you need to supply them Datetime values. Not string.

In .NET Framework, DateTime structure doesn't have any implicit format. It just have date and time values. You get it's string representation when you try to format it. That's why 15/02/2015 will be a string in your case, not a DateTime.

You get FormatException on your Convert.ToDateTime method probably because your d value was not a standard date and time format for your CurrentCulture. You can use custom date and time parsing with using DateTime.ParseExact or DateTime.TryParseExact methods.

string s = "15/02/2015";
DateTime dt;
if(DateTime.TryParseExact(s, "dd/MM/yyyy", CultureInfo.InvariantCulture,
                          DateTimeStyles.None,
                          out dt))
{
    Console.WriteLine(dt);
}

Even if you do that, why do you wanna keep string values in a datetime typed column? That doesn't make sense at all. Even if you do that, since .Rows returns a DataRowCollection, you probably will get Collection was modified; enumeration operation may not execute error since you try to modify your collection while you iterating it.

I suggest you to create another DataTable for your string values and add them to your DateTime values string representation as dd/MM/yyyy format like;

int i = 0;
string d = "";
var stringDataTable = new DataTable();
stringDataTable.Columns.Add("DATE", typeof(String));
stringDataTable.Columns.Add("TIME", typeof(String));

foreach(DataRow dr in dataTable.Rows)
{
   d = ((DateTime)dr["DATE"]).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
   stringDataTable.Rows[i]["DATE"] = d;
   i++;
}
Share:
25,546
Raize Ahamed
Author by

Raize Ahamed

Updated on July 09, 2022

Comments

  • Raize Ahamed
    Raize Ahamed over 1 year

    I am filling the datable from database. It contains two field: DATE, TIME

    Both the fields are datetime column

    I want to iterate through the datatable and change the date format for the DATE column i.e dd/MM/yyyy

    int i = 0;
    string d="";
    foreach (DataRow dr in dataTable.Rows)
    {
        d = dr["DATE"].ToString();
        DateTime date = Convert.ToDateTime(d);
        d = date.ToString("dd/MM/yyyy");
        dataTable.Rows[i]["DATE"] = d;
        i++;
    }
    

    I get the following error

    String was not recognized as a valid DateTime.

    Couldn't store <15/02/2015> in DATE Column. Expected type is DateTime. How can I achieve it?

  • Soner Gönül
    Soner Gönül over 8 years
    @RaizeAhamed Glad to help.