CODEWITHSUNDEEP
c#sorting
claytoncasey01
claytoncasey01

Reputation: 150

Sort DataTable String

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?

Upvotes: 0

Views: 1176

Answers (5)

Roman Marusyk
Roman Marusyk

Reputation: 24619

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"

Upvotes: 0

David Jiménez
David Jiménez

Reputation: 54

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

Upvotes: 0

user897730
user897730

Reputation: 127

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";

Upvotes: 0

Enrique Zavaleta
Enrique Zavaleta

Reputation: 2108

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();

Upvotes: 0

Dave Zych
Dave Zych

Reputation: 21897

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.

Upvotes: 1

Related Questions