BindingSource.Filter problem with filtering by dates

Question

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 Do you know some way to solve this problem? Thanks

Sam Hobbs · Answer

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.

Pravin Ghadge · Answer

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) + #)); } }

Neven Draskovic · Answer

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?

Sam Hobbs · Answer

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.

Neven Draskovic · Answer

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

Sam Hobbs · Answer

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.

Neven Draskovic · Answer

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 2012this is a sample of a query that is created by clicking on the button: (Pocetak >= #2/1/2012# AND Pocetak 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.

Sam Hobbs · Answer

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.

Pravin Ghadge · Answer

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

Neven Draskovic · Answer

@Sam: I took special care not to include time - I construct the query by adding MonthDayYear 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  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).

Sam Hobbs · Answer

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.

Pravin Ghadge · Answer

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