Sorting a datatable by changing column type using LINQ

Question

4.00/5 (1 vote)

See more:

I want to sort a datatable string column but column having date in (mm/dd/yyyy) format.

What I have tried:

I tried following but the data is not getting sorted in valid order.

C#

dtData = dtData.AsEnumerable().OrderByDescending(r => (Convert.ToDateTime(r.Field<string>(ColumnName)))).CopyToDataTable();

Posted 13-Nov-17 3:03am

IamWsk

Updated 13-Nov-17 3:36am

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-11-13T03:36:00

Solution 1

use DateTime.ParseExact Method [^]

dt = dt.AsEnumerable().OrderByDescending(r => (DateTime.ParseExact(r["DateColumnName"] + "", "mm/dd/yyyy", CultureInfo.InvariantCulture))).CopyToDataTable();

if you are worried about null or other string data then DateTime.TryParseExact Method [^] would be the best choice

DataTable dt = new DataTable();
            dt.Columns.Add("DateColumnName");
            dt.Rows.Add("11/14/2017");
            dt.Rows.Add("11/13/2017");
            dt.Rows.Add("11/16/2017");

           

            dt.Columns.Add("NewColumnTemp");
            dt.AsEnumerable().ToList().ForEach(row =>
            {
                string date = row["DateColumnName"] + "";
                DateTime dateTemp;
                DateTime? dateTempNull = null;
                bool isValid = DateTime.TryParseExact(date, "mm/dd/yyyy", CultureInfo.CurrentCulture, DateTimeStyles.None, out dateTemp);
                row["NewColumnTemp"] = isValid ? dateTemp : dateTempNull;

            });
            DataView dv = dt.DefaultView;
            dv.Sort = "NewColumnTemp desc";
            dt = dv.ToTable();
            dt.Columns.Remove("NewColumnTemp");

Posted 13-Nov-17 3:36am

Karthik_Mahalingam

Updated 13-Nov-17 3:52am

v4

Comments

IamWsk 13-Nov-17 9:38am

Thank you @Karthik Banglore. Would it handle the null values?Column has some null or blank values too.

Karthik_Mahalingam 13-Nov-17 9:39am

have updated the solution

IamWsk 13-Nov-17 9:41am

OK, Let me apply the solution.

IamWsk 13-Nov-17 10:38am

Now it is sorting like this
NewColumnTemp
31-01-2015
31-01-2014
30-01-2015

Karthik_Mahalingam 13-Nov-17 10:45am

mm/dd/yyyy

IamWsk 13-Nov-17 11:15am

it is being sort irrespective of year if you see.
It should be like:
31-01-2015
30-01-2015
31-01-2014

Karthik_Mahalingam 13-Nov-17 11:35am

Did you change the format to dd-mm-yyyy ?

Karthik_Mahalingam 13-Nov-17 22:11pm

it works fine

 DataTable dt = new DataTable();
            dt.Columns.Add("DateColumnName");
            dt.Rows.Add("31-01-2015");
            dt.Rows.Add("31-01-2014");
            dt.Rows.Add("30-01-2015"); 

            dt.Columns.Add("NewColumnTemp");
            dt.AsEnumerable().ToList().ForEach(row =>
            {
                string date = row["DateColumnName"] + "";
                DateTime dateTemp;
                DateTime? dateTempNull = null;
                bool isValid = DateTime.TryParseExact(date, "mm-dd-yyyy", CultureInfo.CurrentCulture, DateTimeStyles.None, out dateTemp);
                row["NewColumnTemp"] = isValid ? dateTemp : dateTempNull;

            });
            DataView dv = dt.DefaultView;
            dv.Sort = "NewColumnTemp desc";
            dt = dv.ToTable();
            dt.Columns.Remove("NewColumnTemp");
            /*
             * 31-01-2015
             * 30-01-2015
             * 31-01-2014
             */

IamWsk 14-Nov-17 3:45am

Please add the data rows in below format and try, it wont work.
dt.Rows.Add("1/31/2015");
dt.Rows.Add("1/31/2014");
dt.Rows.Add("1/30/2015");
As your data in mm/dd/yyyy format so change the format too.

Karthik_Mahalingam 14-Nov-17 4:05am


            DataTable dt = new DataTable();
            dt.Columns.Add("DateColumnName");
            dt.Rows.Add("1/31/2015");
            dt.Rows.Add("1/31/2014");
            dt.Rows.Add("1/30/2015");

            dt.Columns.Add("NewColumnTemp",typeof(DateTime));
            dt.AsEnumerable().ToList().ForEach(row =>
            {
                string date = row["DateColumnName"] + "";
                DateTime dateTemp;
                DateTime? dateTempNull = null;
                bool isValid = DateTime.TryParseExact(date, new string[] { "M/dd/yyyy", "MM/dd/yyyy", "M/d/yyyy", "MM/d/yyyy" }, CultureInfo.CurrentCulture, DateTimeStyles.None, out dateTemp);
                row["NewColumnTemp"] = isValid ? dateTemp : dateTempNull;

            });
            DataView dv = dt.DefaultView;
            dv.Sort = "NewColumnTemp desc";
            dt = dv.ToTable();
            dt.Columns.Remove("NewColumnTemp");

IamWsk 14-Nov-17 4:24am

It was still making a string column so I made it Datetime and finally it is working.
dt.Columns.Add("DateColumnName",typeof(DateTime));
object dateTempNull = DBNull.Value;

Thank you Karthik please give stars to my question.

Karthik_Mahalingam 14-Nov-17 5:21am

cool