Sort DataTable String

Question

So I am working on a site that an intern did, we have a date column in out DataTable but it is just a string, so when doing dt.DefaultView.Sort = "DATE ASC" it is sorting weird, here is an example.

Date:
01/31/2015
02/10/2015
02/15/2015
02/15/2014
02/20/2015
02/20/2014
02/21/2014

As you can see by this, it is sorting by the first two mm/dd but year is mixed into this, so we have 2014 dates with 2015 etc. I need to sort first by the year and then by the other two. Any ideas how to do this since the date is just a string?

Convert the strings to actual `DateTime`s similar to this question, then sort? http://stackoverflow.com/questions/919244/converting-a-string-to-datetime — Broots Waymb, Sep 10 '15 at 15:27

score 1 · Accepted Answer · answered Sep 10 '15 at 15:35

I would add a new column to the DataTable that has a DateTime type and sort it by that. That way you get "real" date sorting. If you can edit the SQL procedure that is loading the data into the DataTable to return the date, that would be best, but otherwise you can do that in code:

var dt = GetMyDataTable(); //however you're loading the table
dt.Columns.Add("MyDate", typeof (DateTime));
foreach (DataRow row in dt.Rows)
{
     row["MyDate"] = DateTime.Parse(row["MyStringDate"].ToString());
}
dt.DefaultView.Sort = "MyDate ASC";

Note that if you have a lot of rows, this is probably not the way to go, and the better approach is modifying the procedure to return the date as a DateTime.

score 0 · Answer 2 · edited Sep 10 '15 at 20:24

0

You are ordering the column as string. Put the column as date or use the format YYYY-MM-DD to order the column.

edited Sep 10 '15 at 20:24

Roman Marusyk

23,328
24
73
116

answered Sep 10 '15 at 15:28

David Jiménez

54
4

score 0 · Answer 3 · edited Sep 10 '15 at 20:49

0

I'm not sure it would work but try to add cast because your DATE field is varchar type and sorted as string :

dt.DefaultView.Sort = "CAST(DATE as Date) ASC"

edited Sep 10 '15 at 20:49

Dave Zych

21,581
7
51
66

answered Sep 10 '15 at 15:29

Roman Marusyk

23,328
24
73
116

If you aren't sure if it will work, it should be a comment, not answer. – Broots Waymb Sep 10 '15 at 15:33

score 0 · Answer 4 · answered Sep 10 '15 at 15:35

0

If you want/can use Linq then this should work:

var sortedDataTable = 
  (from row in yourDataTable.AsEnumerable()
   let correctDate= Convert.ToDateTime(row.Field<string>("DATE"))     
   orderby correctDate
   select row).CopyToDataTable();

answered Sep 10 '15 at 15:35

Enrique Zavaleta

2,098
3
21
29

score 0 · Answer 5 · answered Sep 10 '15 at 15:55

0

How about adding a Column Expression?

table.Columns.Add(new DataColumn("YMD") {
  Expression = "SUBSTRING(Date,7,4)+SUBSTRING(Date,1,2)+SUBSTRING(Date,4,2)"
});
var view = table.DefaultView;
view.Sort = "YMD";

answered Sep 10 '15 at 15:55

user897730

121
1
4

Sort DataTable String

5 Answers5