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.

where and how are you using this data table? I'd say you should be formatting the date when you use it on your UI layer (for instance as data source) but not before. — Claudio Redi
– Claudio Redi, Commented Nov 24, 2010 at 21:34
I'm binding it to a GridView. The columns are dynamic, so I can't format it in Design View. The index of the column can be different as well, so it's not as easy as changing it in the RowDataBound event either. — Steven
– Steven, Commented Nov 24, 2010 at 21:37
If that's the case, then you may want to make the column type a string and format it before you insert the values. — Aaron Daniels
– Aaron Daniels, Commented Nov 24, 2010 at 21:42
@Aaron He's not manually inserting the values, everything is taken care of internally by Load() — Josh Stodola
– Josh Stodola, Commented Nov 24, 2010 at 21:50
I didn't see the sp. I would change the sp to return formatted strings instead of datetime values. Basically do the formatting in the sp, as Josh points out. — Aaron Daniels
– Aaron Daniels, Commented Nov 25, 2010 at 2:48

Jeff the Bear · Accepted Answer · 2010-11-24 21:55:10Z

12

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"]);
}

edited Nov 24, 2010 at 21:55

answered Nov 24, 2010 at 21:48

Jeff the Bear

5,6533 gold badges25 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josh Stodola · Accepted Answer · 2010-11-24 21:47:32Z

4

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)

answered Nov 24, 2010 at 21:47

Josh Stodola

82.7k48 gold badges187 silver badges229 bronze badges

Comments

Aaron Daniels · Accepted Answer · 2010-11-24 21:40:54Z

2

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.

edited Nov 24, 2010 at 21:40

answered Nov 24, 2010 at 21:33

Aaron Daniels

9,6746 gold badges48 silver badges58 bronze badges

Collectives™ on Stack Overflow

Changing DataType of column in DataTable from DateTime to String

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related