c# - Convert string to datetime value in LINQ

Question

Suppose I have a table storing a list of datetime (yyyyMMdd) in String format. How could I extract them and convert them into DateTime format dd/MM/yyyy ?

e.g. 20120101 -> 01/01/2012

I have tried the following:

var query = from tb in db.tb1 select new { dtNew = DateTime.ParseExact(tb.dt, "dd/MM/yyyy", null); };

But it turns out the error saying that the ParseExact function cannot be recgonized.

Answer 1

It's probably worth just doing the parsing locally instead of in the database, via AsEnumerable:

var query = db.tb1.Select(tb => tb.dt)
                  .AsEnumerable() // Do the rest of the processing locally
                  .Select(x => DateTime.ParseExact(x, "yyyyMMdd",
                                                CultureInfo.InvariantCulture));

The initial select is to ensure that only the relevant column is fetched, rather than the whole entity (only for most of it to be discarded). I've also avoided using an anonymous type as there seems to be no point to it here.

Note how I've specified the invariant culture by the way - you almost certainly don't want to just use the current culture. And I've changed the pattern used for parsing, as it sounds like your source data is in yyyyMMdd format.

Of course, if at all possible you should change the database schema to store date values in a date-based column, rather than as text.