BindingSource Filter by date

Question

Hello.

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.

But it throws an EvaluateException: "The expression contains undefined function call DATE()."

Although if I execute the SQL statement directly, I can use the DATE() function in filter.

P.S. I use MYSQL DB with the Connector/Net 5.2

How can I solve this problem?

Thank You all for suggestions.

Answer 1

Is myDateField the name of the field in the dataset? I think you want an expression like this:

myBindingSource.Filter = "myDateField = " & myDateTime.Date.ToString()

Answer 2

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).

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. :)

Answer 3

Thank you Tom 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:

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";
}

Answer 4

The getSqlDate function is not needed. You can use String.Format() to format dates:

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));

Answer 5

A correction to the answer: Accoring to msdn ,to get the correct date the mm in

yyyy-mm-dd

would have to be capitalized like so;

yyyy-MM-dd

to get a correctly formatted date.