Changing DataType of column in DataTable from DateTime to String

Question

I'm loading data from my database into a DataTable, and one of the columns is a date field.

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "MySP";
    cmd.CommandType = CommandType.StoredProcedure;

    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        dt.Load(rdr);
    }
}

I'd like to format that column so that instead of containing a full date, it will be formatted like "MM/DD/YYYY".

I've tried looping through each row in the table and changing the cell for that column, but I get an error saying that the string isn't a valid DateTime object.

I tried changing the column DateType to a string, but I get an error saying I can't change the DateType after the table is filled.

How can I do this? This seems like such a simple thing, but I'm having so much trouble with it.

Answer 1

As a work around you could create a new column and store the formatted date there:

dt.Columns.Add("DateStr");

foreach (DataRow dr in dt.Rows)
{
    dr["DateStr"] = string.Format("{0:MM/dd/yyyy}", dr["OriginalDate"]);
}

Answer 2

Change your stored procedure to cast the field to a string using CONVERT...

SELECT CONVERT(varchar, DateFieldName, 101) AS DateFieldNameFormatted

The 101 is the format code for mm/dd/yyyy (see link for other codes/formats)

Answer 3

You can't format a DateTime struct value. Don't think of it that way. It's purpose is to hold a value representing:

an instant in time, typically expressed as a date and time of day.

You can only specify the format when you convert the value of that instance to a string. Leave your DataTable schema as is. Wherever you need to format it, look at some of the formats you can use with the ToString(string) method.