Sort DataTable based on date column(dd MMM yyyy)

Question

I have a dataset ds and i need to sort the datatabel of the same ds based on a column and the column name is Date. The values on this column is in 'dd MMM yyyy' format.

http://stackoverflow.com/a/14031086/1134076 < This what you are looking for — Dr Schizo, Jul 26 '13 at 06:52
It's not a question, it's request. No code, no effort just specification. — Kamil Budziewski, Jul 26 '13 at 06:55
possible duplicate of [How do you Sort a DataTable given column and direction?](http://stackoverflow.com/questions/5005658/how-do-you-sort-a-datatable-given-column-and-direction) — Ankur, Jul 26 '13 at 07:13

Damith · Answer 1 · 2013-07-26T07:13:19.237

0

If you can't change the column type to Date then try Linq solution as below

var recs = dataset.Tables[0].AsEnumerable()
    .OrderBy(x =>DateTime.ParseExact(
             x.Field<string>("Date"),
                           "dd MMM yyyy", 
                           CultureInfo.InvariantCulture));

edited Jul 26 '13 at 07:13

answered Jul 26 '13 at 07:02

Damith

62,401
13
102
153

You have a typo in there should be dd MM yyyy and DateTime.ParseExact() :) good answer – Dr Schizo Jul 26 '13 at 07:03
@Onam sorry, i did't get you – Damith Jul 26 '13 at 07:07
Look at the format of the date you are converting it to dd MMM yyyy. Note the 3 characters to represent a month. Also DateTime.ParaseExact is misspelled and just noticed CultureInfo.InvariantCultur needs to be CultureInfo.InvariantCulture (note the 'e' at the end). Other than that perfect :) – Dr Schizo Jul 26 '13 at 07:10
@Onam - OP has `MMM`, not `MM`. @Damith - You wrote `ParaseExact` instead of `ParseExact`. – Corak Jul 26 '13 at 07:10
@Onam and Corak, Thanks both of you. – Damith Jul 26 '13 at 07:15

Curt · Answer 2 · 2013-07-26T07:28:01.853

-1

That's going to be a problem particularly if it's a large table. The best answer of course, would be to redefine the column as a date type. That way it'll sort and join efficiently. If the table is not large, though, you can reformat the date string to ISO format (YYYY-MM-DD), and it will sort correctly. You didn't mention which database you using, and the string and date functions vary among platforms: here's a SQL Server example:

 SELECT CAST([Date] AS Date) AS real_date,
        my_other_fields
   FROM MyTable

I'm at home, so I don't have a SQL Server to test this on right now (I can update the example in the morning if it doesn't work), but that date format is pretty unambiguous to parse, so I expect that the cast will work okay.

If you want to take care of this entirely on the application side, it should be fairly straightforward to write a string function will reorder the year, month and day in the date string.

edited Jul 26 '13 at 07:28

answered Jul 26 '13 at 07:00

Curt

5,518
1
21
35

1

In fact i am using sql server 2008. – Anu Basheer A Jul 26 '13 at 07:09
@AnuBasheerA I've added a snippet to my answer: I'm pretty sure it will work as written, but will confirm as soon as I come back to the office. – Curt Jul 26 '13 at 07:28
@AnuBasheerA And, having checked, the cast works just fine (as long as the string is well-formed). – Curt Jul 26 '13 at 23:35

Sort DataTable based on date column(dd MMM yyyy)

2 Answers2