在 LINQ 中将字符串转换为日期时间值

问题描述

假设我有一个表，以 String 格式存储日期时间 (yyyyMMdd) 列表.我如何提取它们并将它们转换为日期时间格式 dd/MM/yyyy ?

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 ?

例如20120101 -> 01/01/2012

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

我尝试了以下方法:

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

但结果是ParseExact函数无法识别的错误.

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

推荐答案

通过 AsEnumerable 在本地而不是在数据库中进行解析可能是值得的:

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.

顺便说一下，请注意我是如何指定不变文化的 - 您几乎肯定不想只想使用当前文化.我更改了用于解析的模式，因为听起来您的 source 数据采用 yyyyMMdd 格式.

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.