How do I sort DataTable by ascending dates?

Question

My c# code downloads stock quotes into a DataTable object. The data comes as descending dates on the first column. I would like to sort this table as ascending dates. I've tried the following:

table.DefaultView.Sort = "Date";

sorts the table as if it were of string type, e.g.: "1/1/1994", "1/1/1995", "1/1/1996" instead of the desired "1/1/1994", "1/2/1994", "1/3/1994".

Now, the following raises InvalidCastException.

var newTable = table.AsEnumerable().OrderBy(r => r.Field<DateTime>("Date")).CopyToDataTable();

Note: This sorting happens in a method that returns a DataTable object.

[UPDATE] this is how I create and fill the table from a List where each entry is formatted as e.g.: "1/1/1994,21.01,22,21,21.01,23131,21.01"

        List<string> list = Downloaders.DownloadContentToList(symbol);

        DataTable table = new DataTable(symbol);
        table.Columns.Add("Date");
        table.Columns.Add("Open");
        table.Columns.Add("High");
        table.Columns.Add("Low");
        table.Columns.Add("Close");
        table.Columns.Add("Volume");
        table.Columns.Add("Adj Close");

            for (int i = 0; i < list.Count; i++)
        {
            string[] cols = list[i].Split(',');

            DateTime date = Convert.ToDateTime(cols[0]);
            double open = Convert.ToDouble(cols[1]);
            double high = Convert.ToDouble(cols[2]);
            double low = Convert.ToDouble(cols[3]);
            double close = Convert.ToDouble(cols[4]);
            double volume = Convert.ToDouble(cols[5]);
            double adjClose = Convert.ToDouble(cols[6]);
            table.Rows.Add(date, open, high, low, close, volume, adjClose);
        }

Solution 1

When Creating Your Table You Should add "date" column as :

table.Columns.Add("Date", typeof(DateTime));

then :

table.DefaultView.Sort = "Date desc";

Solution 2

When you create the datatable you have to specify that it is a date even if it is in string format:

table.Columns.Add("dateValue", typeof(DateTime?));

    var orderedRows = from row in dt.AsEnumerable()
                      orderby  row.Field<DateTime>("Date")
                      select row; 
    DataTable tblOrdered = orderedRows.CopyToDataTable();

or:

var orderedRows = from row in dt.AsEnumerable()
                      let date = DateTime.Parse(row.Field<string>("Date"), CultureInfo.InvariantCulture)
                      orderby date 
                      select row;

This is a similar question but I think the answer also applies to you.

Solution 3

The "Date" column should be added as so:

table.Columns.Add("Date", typeof(DateTime));

rather than

table.Columns.Add("Date");

Comments

[olhodolago]

"table.Columns.Add("dateValue", typeof(DateTime?))" gives me "DataSet does not support System.Nullable<>", correct is "table.Columns.Add("dateValue", typeof(DateTime))"