c# - 将用户给定日期(2017 年 1 月或 2 月 1 日)转换为时间戳

Question

我正在尝试将用户输入的日期解析为 MySQL 时间戳格式 (YYYY-MM-DD)。用户输入可能类似于:

input -  wanted conversion1) January - ThisYear-01-01, ThisYear-01-312) February 2017 - 2017-02-01, 2017-02-283) 01. April 2016 - 2016-04-014) 5.4.15 - 2015-04-05

P.S.: The examples above are the suggested formats that we want to support (supporting only some of them would be also fine).The users are not random or international, they will always write and understand dates in this format (Day Month Year).

Handling the missing year entry or the zero (like 5/4 in #4) isn't a problem but finding a proper way to handle the mentioned possible date input formats (DD MM YY, DD MM, DD Month, Month, Month YY, DD Month YY....etc.) with something that doesn't look very ugly and long in code is a little bit hard for me to imagine.

P.S.:

D,DD,MM,YY,YYYY are short for the numerical input.

Month is for the word input variant.

Could you please tell me if there is anything that could help me to make this process easier/more readable or at least point me to the right direction?

Thanks

---

Update #1:By looking again to my question above i see that it's missing some background information, but i didn't want to write a long description to it, just only to the wanted function. Sorry.

So here are some general infos about the Program:The Program is a Chatbot written in C# (UWP) which accepts a user request in natural language and give back the requested info (if recognized) from a mySQL DB (DB is based on a OSTicket support system).

Internally we send the user input to LUIS.ai to get it recognized and we get back the intents and entities from the service, which we then parse to a SQL Query and send it to the DB.The results from the DB are then sent back to the user.

Many parsed queries work perfectly, that's why i want now to extend it by letting the user give a certain date in the request (e.g. give me all the support tickets from April).What i only want is to take this new input "April" and convert it to a MySQL TimeStamp format, so that it would be also recognized from MySQL.

My current approach is to build a string like this:

string convertedTS = year + "-" + month + "-" + day;

并使用 3 个函数尝试检测所有三个变量。但现在就这样了。

例如“给我四月份的所有门票”:四月(从 LUIS AI 识别为给定日期)将转换为 (04)年份将从

DateTime.Today.Year

也插入了 (2018-04)第一天始终为 01，该月的最后一天为:

DateTime.Today.Year

示例的最终查询:

select ..........between '2018-04-01' AND '2017-04-30'

Answer 1

如果您担心年、月、日等的格式，那么您就错了。您有 C#，并且有来自用户的日期字符串。您关心的是将该字符串转换为 C# DateTime 值。 MySql 没有参与其中。

一旦您获得了 DateTime 值，就可以让您的连接提供商担心通过参数化查询进行格式化:

DateTime d = GetMyDateTimeValueFromUser();
string sql = "pretend SQL comnand with a datetime @variable";
using (var cn = new MySqlConnection("connection string here"))
using (var cmd = new MySqlCommand(sql, cn))
{
    cmd.Parameters.Add("@variable", MySqlDbType.Timestamp).Value = d;
    cn.Open();

    //pick one.
    cmd.ExecuteReader();
    cmd.ExecuteNonQuery();
}

不需要特殊的 SQL 格式。 如果您没有使用参数化查询，您的做法就不正确!这不仅仅适用于 DateTime 值。 SQL 语句中使用的所有数据都应以这种方式处理。这对安全和性能的影响远远超出了简单的日期值。传递参数，或者回家(我的意思是:回家。不要写糟糕的代码。我们不再需要了。)

由于它适用于该问题，这意味着问题完全与将字符串解析为 C# DateTime 值有关。将此数据移至 SQL 的下一步是不相关的。值得庆幸的是，.Net 为您提供了一些选择。具体来说，看一下 Parse 系列函数，包括:

DateTime.Parse()
DateTime.TryParse()
DateTime.ParseExact()
DateTime.TryParseExact()

后两者允许您指定一组允许的格式，这些格式可以与系统实际看到的格式相匹配。 NuGet 可以成为该领域的进一步资源。

你说那是什么？您想允许用户输入任何内容吗？那是行不通的。时期。真正的人类并且将会想出比你所能处理的更多的输入日期的方法。更不用说当英国公民在您的系统中输入值 1/2/2018 时您需要知道该怎么做，因为该人几乎肯定认为这意味着 2018 年 2 月 1 日，而不是 1 月 2 日像这样的文化差异意味着如果前端没有某种上下文输入过滤器，就不可能接受日期输入。您必须关注您的用户界面，以帮助您的用户创建您的代码能够理解的值...但我们在问题中没有足够的信息来提供该领域的任何指导。