CODEWITHSUNDEEP
c#stringdatedatetimedatatable
Raize Ahamed
Raize Ahamed

Reputation: 385

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

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?

Upvotes: 2

Views: 29020

Answers (1)

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98750

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++;
}

Upvotes: 1

Related Questions