Get custom string from SQL and turn into c# date object to sort by

Question

I have a table that has a sql column with a date as a string like 'January 1, 2018'. I'm trying to turn that into a DateTime object in C# so I can use it to sort a list by. I'm currently grouping everything by the ID so I can return the highest revision. This is working great but I also need to OrderByDescending date from a the column that represents a date. The below code will order everything alphanumerically but I need to sort by DateTime.

    using (dbEntities entities = new dbEntities())
    {
        var db = entities.db_table
        .GroupBy(x => x.ID) //grouping by the id
        .Select(x => x.OrderByDescending(y => 
                     y.REVISIONID).FirstOrDefault());
       return db.OrderBy(e => e.Date_String).ToList(); 
    }

Thanks, I appreciate any help on this!

see this SO for how to parse your string to datetime in C#: https://stackoverflow.com/questions/919244/converting-a-string-to-datetime -- if you need the datetimes for anything other than sorting you can probably add a field to your model and maintain both your db string and your datetime, in this case do your conversion and sort in 2 separate steps. If you don't need it outside of the sort, utilize one of the linq statements from the answers. — user7396598, Mar 20 '18 at 18:58
Why don't you convert the string to a date within the database instead on the client? — dnoeth, Mar 20 '18 at 18:59
for future proofing, you should probably try to convert that field if at all possible and just maintain a datetime object in the database. It will save you many headaches. — user7396598, Mar 20 '18 at 18:59

score 3 · Accepted Answer · answered Mar 20 '18 at 18:52

3

You'll need to materialize the objects and use LINQ-to-Objects to do the conversion to a C# DateTime.

return db.AsEnumerable().OrderBy(e => DateTime.Parse(e.Date_String)).ToList();

If at all possible, I would strongly recommend changing your column to a datetime2 or datetimeoffset at the database level, though.

answered Mar 20 '18 at 18:52

StriplingWarrior

151,543
27
246
315

Exactly what needed! I'll look into changing the column at db level. – dmod40 Mar 20 '18 at 19:16
@dmod40: Excellent. If you *don't* end up changing the data type, do be aware that `DateTime.Parse` will behave differently on machines with different cultures, so you might want to add `CultureInfo.InvariantCulture` as an argument, or use `ParseExact` to avoid flaky behavior. But just changing the underlying data type will avoid this problem in the first place. – StriplingWarrior Mar 20 '18 at 19:34

Falle1234 · Answer 2 · 2018-03-20T19:34:03.577

0

If you don't mind some of the work being done on the client side you could do something like this:

using (dbEntities entities = new dbEntities())
{
    var db = entities.db_table
    .GroupBy(x => x.ID) //grouping by the id
    .Select(x => x.OrderByDescending(y => 
                 y.REVISIONID).FirstOrDefault()).ToList();
   return db.OrderBy(e => DateTime.Parse(e.Date_String)).ToList(); 
}

The parsing of the DateTime needs to be modified so it matches the format in the database, but otherwise it should work.

edited Mar 20 '18 at 19:34

answered Mar 20 '18 at 18:53

Falle1234

5,013
1
22
29

1

Did you mean "some of the work being done client side"? I would suggest `AsEnumerable` over `ToList`. – NetMage Mar 20 '18 at 18:55
Of course. I am currently living in a web app world, so in my view it goes: client -> server -> database. But your right :) – Falle1234 Mar 20 '18 at 18:58

Get custom string from SQL and turn into c# date object to sort by

2 Answers2