C# BindingSource 按日期过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/400283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BindingSource Filter by date
提问by Janis Veinbergs
I want to filter values from database based on date.
我想根据日期从数据库中过滤值。
Date in a database contains values like this: 2008-12-28 18:00:00. And my class has a DateTime variable depending on which I want to filter. Ideally it would work like this:
myBindingSource.Filter = "DATE(myDateField) = myDateTime.Date"+ adjusting myDateTime.Date format as needed.
数据库中的日期包含如下值：2008-12-28 18:00:00。我的班级有一个 DateTime 变量，具体取决于我要过滤的内容。理想情况下，它会像这样工作：
myBindingSource.Filter = "DATE(myDateField) = myDateTime.Date"+ 根据需要调整 myDateTime.Date 格式。
But it throws an EvaluateException: "The expression contains undefined function call DATE()."
但它抛出一个 EvaluateException：“表达式包含未定义的函数调用 DATE()。”
Although if I execute the SQL statement directly, I can use the DATE() function in filter.
虽然如果我直接执行SQL语句，我可以在过滤器中使用DATE()函数。
P.S. I use MYSQL DB with the Connector/Net 5.2
PS 我将 MYSQL DB 与 Connector/Net 5.2 一起使用
How can I solve this problem?
我怎么解决这个问题？
Thank You all for suggestions.
谢谢大家的建议。
采纳答案by Rosco
The getSqlDate function is not needed. You can use String.Format() to format dates:
不需要 getSqlDate 函数。您可以使用 String.Format() 来格式化日期：
String.Format("{0:yyyy-MM-dd} 00:00:00", myDateTime)
OR
或者
myDateTime.Date.ToString("yyyy-MM-dd") + " 00:00:00"
You could filter the binding source like this:
您可以像这样过滤绑定源：
myBindingSource.Filter = String.Format("myDateField >= '{0:yyyy-MM-dd}' AND myDateField < '{1:yyyy-MM-dd}'", myDateTime, myDateTime.AddDays(1));
回答by DCNYAM
Is myDateField the name of the field in the dataset? I think you want an expression like this:
myDateField 是数据集中字段的名称吗？我想你想要这样的表达：
myBindingSource.Filter = "myDateField = " & myDateTime.Date.ToString()
回答by Tom H
Are you asking how to eliminate the time portion of the datetime in the filter? I'm not too familiar with MySQL, but if you use any kind of function that returns the date portion of a datetime then you are likely to kill any chance of using an index on that column for the query (existing or future index).
您是在问如何消除过滤器中日期时间的时间部分吗？我对 MySQL 不太熟悉，但是如果您使用任何类型的函数返回日期时间的日期部分，那么您可能会扼杀在该列上使用索引进行查询（现有或未来索引）的任何机会。
Your best bet is to create a filter on the front end that checks for a range that is only for your given filter date. For example:
最好的办法是在前端创建一个过滤器，以检查仅适用于给定过滤器日期的范围。例如：
myBindingSource.Filter = "myDateField >= " & <code to create a string representing 12AM of your date> &
" myDateField < " & <code to create a string for 12AM of the next day>
Sorry for not having exact code, but I'm a SQL developer and my lack of VB/C# skills would require me to take a lot more time to come up with the functions then it would probably take you. :)
抱歉没有确切的代码，但我是一名 SQL 开发人员，我缺乏 VB/C# 技能需要我花更多的时间来设计函数，然后它可能需要你。:)
回答by Janis Veinbergs
Thank you Tom H.
谢谢汤姆 H。
Yes, i wanted to eliminate the time portion of the datetime in the filter and your suggestion works perfectly.
是的，我想消除过滤器中日期时间的时间部分，您的建议非常有效。
I`ll leave the complete solution for others:
我会把完整的解决方案留给其他人：
myBindingSource.Filter = "myDateField >= '" + getSqlDate(myDateTime) + "' AND myDateField < '" + getSqlDate(myDateTime.AddDays(1)) + "'";
where getSqlDate function is:
其中 getSqlDate 函数是：
string getSqlDate(DateTime date) {
string year = "" + date.Year;
string month = (date.Month < 10) ? "0" + date.Month : "" + date.Month;
string day = (date.Day < 10) ? "0" + date.Day : "" + date.Day;
return year + "-" + month + "-" + day + " 00:00:00";
}
回答by B4ndt
回答by user6717817
For search between two date in DataGridView you can use this code :
要在 DataGridView 中的两个日期之间进行搜索，您可以使用以下代码：
BindingSource1.Filter = "F5 >= '" + maskedTextBox1.Text + "' And " + "F5 <= '" + maskedTextBox2.Text + "'";
BindingSource1 : my datagridview datasourc load in BindingSource1 .
F5 : name of your header column in datagridview .
maskedTextBox1 : for get first date .
maskedTextBox2 : for get second date .
回答by user6717817
For search between two date in DataGridView you can use this code :
要在 DataGridView 中的两个日期之间进行搜索，您可以使用以下代码：
BindingSource1.Filter = "F5 >= '" + maskedTextBox1.Text + "' And " + "F5 <= '" + maskedTextBox2.Text + "'";
BindingSource1 : my datagridview datasourc load in BindingSource1 . F5 : name of your header column in datagridview . maskedTextBox1 : for get first date . maskedTextBox2 : for get second date .
BindingSource1 ：我的 datagridview 数据源加载到 BindingSource1 中。F5：datagridview 中标题列的名称。maskedTextBox1 ：用于获取第一个日期。maskedTextBox2：获取第二个日期。
Be successfull "Arn_7"
成功“Arn_7”
