BindingSource.Filter problem with filtering by dates

Hey

I use the filter method of BindingSource to filter it by one or multiple conditions, one of conditions being Date. First problem is that when I try to filter with this expression (e.g Date = #2/10/2012#) it returns no results (although there should be some). I've gone around that by making everything a date span like Date => #2/9/2012# AND Date<= #2/11/2012# (the date format is mm/dd//yyyy). This span will give me only the events that happened on 10th of February 2012 (it is like the filter ignores the '=' sign). This works properly for all dates except for the first and the last day of a month (because I programmatically subtract/add one day and make a span so for the first of the month it would be #2/0/2012# and that is an obvious error).

Do you know some way to solve this problem?
Thanks

hi Neven,

can u explain me why u r subtracting/adding 1 day in date.

Have you tried the exact same filter in the where clause of a SQL query that you execute directly somehow (not in your code)? My guess is that it is not a problem unique to the use of the BindingSource.Filter property.

Are you sure it is a date field without a time? The behavior you describe sounds like there is also a time with the date.

@Sam: I took special care not to include time - I construct the query by adding Month,Day,Year properties of dateTimePicker to a string. I managed to "fix" the problem by making if-else statements (for first, last, and the rest of the days of the month) - this way I avoided the 0 and 32 dates in a month. But, while testing, one weird thing happened: When I need to filter the list to display only events on February 1st it includes events from January 31 (includes the date it should not), but when I want the results for February 2nd it displays proper results. Both use the same operator >= or <= but produce same results (I tried with < and >, I get the same results). Do you know what is causing this? 
P.S I tried my queries in SQL, they produce proper results

@Pravin: Like I mentioned in my question, I subtract and add a day because if I use the equal operator the query always returns empty set (like nothing happened that day), so I need to use a time interval (essentially saying: after Date1 but before Date2).

Neven,

can u paste ur code?
so that i can help u better.

Yes, Neven I know that the query does not include time. I was asking about the database field, not the query. I am sorry I was not clear.

The best way to get the most relevant answer is to post something that recreates the problem. It helps to make that as small as possible that duplicates the problem. That sounds like a lot of work, but it is the type of thing that I have done before to get answers. And for this it should be quitre easy to do. Tell us how to create a table and give us a query and a small peice of code that we can use to see what is happening. Many times when I do that to get help, I solve the problem myself.

Pravin asked for the source code. You can post the source code as Pravin requested but it might be even more effective if you post a complete simple sample of the problem.

First - the database field does contain the time parameter, but I cant change that, it is my clients database, so I have to work with what I have. 
Second, the code sample - there are 4 tables in my clients database that my application is using (not at the same time), each one contains informations about the work done by one type of their employees and my job is to make an application that generates reports for data from those tables (the tables do not have the same schema, and names of fields are not same). Naturally, they want to be able to filter that data and generate the report with filtered data. The filtering is done by either employee's ID, the workplace that they occupied at a particular time, date of that event and more -all in all, there are 5 different conditions that can be used to filter the     table(s) - every condition can be used individually or in combination with others. 
Now, since all other conditions work properly (they are simple numerical or textual values), except for the date condition, I'll post just the code that creates the date part of the query (the code is long so I uploaded it under name: "QueryBuilder").
As you can see, the checkBox checking adds the text of date condition to a list (Lista). Every condition has a corresponding checkBox that, when checked, adds a string item to the list that contains the text of a query. When user enters all the conditions he wants he clicks a button that creates the query that filters the BindingSource. This is the code that does that:
 
private void button5_Click(object sender, EventArgs e)
{
string upit = null;
upit = String.Join(" AND ", Lista.ToArray(), 0, Lista.Count);
textBox4.Text = upit;
Lista.Clear();
} 

The textBox line is just so I can see the query before I execute it, it will be removed before distribution.
Lets say that user wishes to create the report that filters the BindingSource only by date (he selects the starting and ending date from 2 dateTimePicker's) and lets say he wants the report for February 1st 2012,this is a sample of a query that is created by clicking on the button:
 
(Pocetak >= #2/1/2012# AND Pocetak <= #2/2/2012#) //The same text is shown in TextBox
 
After that, the BindingSource is filtered by clicking on another button (again only because of testing purposes, when I'm satisfied with results, I'll just add the code that creates the query). This is a part of the code that handles generation of a report with filtered BindingSource:

RDS.Name = "KontrolorRadPojedinacni";
RDS.Value = (this.kontrolorRadPojedinacniBindingSource.Filter = textBox4.Text); \\ this line handles the filtering
this.reportViewer1.LocalReport.DataSources.Add(RDS);
this.reportViewer1.LocalReport.SetParameters(Ime);
this.reportViewer1.LocalReport.SetParameters(Prezime);
this.reportViewer1.LocalReport.SetParameters(ID);
this.reportViewer1.LocalReport.ReportEmbeddedResource = "Kontrolor.Izvjestaji.KontrolorRad(Pojedinacni).rdlc";

I think I gave you all the relevant informations, so please help me with this problem. Thanks again for your help and patience so far.

hi Neven,

try this,

  private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
           
            if (checkBox1.Checked)
            {
                if (NazivAktivneBaze == "KontrolorRad")
                {
 Lista.Add("(Pocetak between #" + Convert.ToDateTime( dateTimePicker1.Value.ToShortDateString()).ToString("MM/dd/yyyy") + "#  and
  #" + Convert.ToDateTime( dateTimePicker2.Value.ToShortDateString()).ToString("MM/dd/yyyy") + "#)");
}
}

I think you did not give us all the relevant information. I think you did not tell us the most important; the one piece of information that could blow this case wide open.

What is the data type of the field in SQL Server? Please tell us that.

Oh sorry, missed it - they are all datetime.
@Pravin - Ok I'll try that.
Thanks for your answers

Then that is the problem. If you want to check if a date is a certain day or before, you need to set the time to 23:59:59 (11:59:59 PM). Do you understand now? I expected that is what is happening but since you were overlooking the real cause, you did not realize what the real cause is and did not provide the relevant information.

Oh, and for what it is worth, I just got through posting a message in another web site's forum in which I called myself an idiot for overlooking something I should have known better. In this case, I assume you have not worked with comparing datetime data before now so it is understandable that you did not realize.

Well I did, but in SQL, not in BindingSource.Filter - in SQL I would simply use BETWEEN. I tried it in my filter, the compiler throws me an error. I think I solved the problem the way Pravin suggested, but I still have some testing to do so I can't tell if that is the right answer or not. If you have some idea on how I could solve this more elegantly please share, I would appreciate every suggestion.

P.S Do you know how do I set time property of a Filter query - I tried #2/2/2012 23/59/59# it doesn't work?