Can filter a date but cannot filter a datetime?

Question

I have an application that can filter a datagridview based on date using a datetimepicker. The "date" column in my database is a datetime data type, hence it will contain a date and time stored within but there is some data which have only dates. My problem is my datetimepicker filter can only filter those data with date = 12:00:00 AM. Those data which contains time other than that cannot be filtered when I chose the date using datatimepicker. I don't know what's the problem. Here is my code :

public trackInput()
    {
        InitializeComponent();
        dataGridView1.Visible = false;
        webBrowser1.Location = new Point(12, 141);
    }

    /*private void trackInput_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'trackingBMSDATADataSet.BRDATA' table. You can move, or remove it, as needed.
        this.bRDATATableAdapter.Fill(this.trackingBMSDATADataSet.BRDATA);

    }*/
    private void trackBtn_Click(object sender, EventArgs e)
    {

        dataGridView1.Visible = true;
        if (dataGridView1.Visible == true)
        {
            webBrowser1.Location = new Point(12, 397);
        }
        //DataTable dt = null;
        string connoInput = textBox1.Text;
        string conString = Properties.Settings.Default.BMSDATAConnectionString;
        using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\TrackCon\TrackCon\BMSDATA.sdf;Persist Security Info = True;Password=Gdex123$"))
        {

                string Ids = "conno= '" + System.Text.RegularExpressions.Regex.Replace(textBox1.Text.Trim(), @"\s*\n\s*", "' OR conno= '") + "'";
                SqlCeCommand com = new SqlCeCommand("SELECT conno,cmpsno,ctrx,dsysdate,cstnno,corigin FROM BRDATA WHERE " +Ids, con);
                SqlCeDataAdapter adap = new SqlCeDataAdapter(com);
                DataSet set = new DataSet();
                adap.Fill(set);
                if (set.Tables.Count > 0)
                {
                    bRDATABindingSource1.DataSource = set.Tables[0];
                }
                bRDATABindingSource1.Filter = null;
                dataGridView1.DataSource = bRDATABindingSource1;
                con.Close();
        }
    }

    private void trackMPSbtn_Click(object sender, EventArgs e)
    {
        dataGridView1.Visible = true;
        if (dataGridView1.Visible == true)
        {
            webBrowser1.Location = new Point(12, 397);
        }
        //DataTable dt = null;
        //string connoInput = textBox1.Text;
        string conString = Properties.Settings.Default.BMSDATAConnectionString;
        using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\TrackCon\TrackCon\BMSDATA.sdf;Persist Security Info = True;Password=Gdex123$"))
        {
            dataGridView1.DataSource = bRDATABindingSource1;
            string Ids = "cmpsno= '" + System.Text.RegularExpressions.Regex.Replace(textBox2.Text.Trim(), @"\s*\n\s*", "' OR cmpsno= '") + "'";
            con.Open();
            SqlCeCommand com = new SqlCeCommand("SELECT conno,cmpsno,ctrx,dsysdate,cstnno,corigin FROM BRDATA WHERE " + Ids, con);
            SqlCeDataAdapter adap = new SqlCeDataAdapter(com);
            DataSet set = new DataSet();
            adap.Fill(set);
            if (set.Tables.Count > 0)
            {
                bRDATABindingSource1.DataSource = set.Tables[0];
            }
            bRDATABindingSource1.Filter = null;
            dataGridView1.DataSource = bRDATABindingSource1;
            con.Close();
        }
    }

    private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
    {
        bRDATABindingSource1.Filter = string.Format("dsysdate = #{0:d/M/yyyy HH:mm tt}#", dateTimePicker1.Value.ToLongDateString());
    }

Answer 1

I have to make several comments:

1st: don't ever compose queries concatenating texts. This is subject to SQL injection,even if you try to clean the input. Use stored procedures or parameterized queries instead: they are totally immune to SQL injection.

2nd: use parameters because you can simply pass values, in its original form, not converting them to text. This will remove part of the problem. You can use stored procedures or parameterized queries.

2nd & 1/2: there are other reasons to use parameters, which has to do with performance.

3rd: probably the filtering by datetime will function, but you cannot assure it. Do you want the user to be able to filter by exact datetime, including seconds and fractions of seconds? I think that's not a good idea. Please, re-design it or explain what you want to do. It looks it doesn't have much sense. Maybe you should "round" the date up to seconds or minutes, before storing it in the DB. You should consider that datetime is not exact. It works somewhat like a float, so you can make a direct comparison of this values without having trouble. That said, I wait for your comments to help you improve your design.

ADDED: As you say the time part is not important, you can remove it on insertion, or on comparison (while comparing the datetime column with the parameter), using this "trick":

SELECT (CAST(FLOOR(CAST(YourDateTimeColumn as FLOAT)) AS DateTime))

Dates are stored internally as numbers of days from a base date. The decimal part represents the fraction of a day (hh:mm:ss...). So, if you get rid of the decimal, you keep the date part only, and you can safely compare it with a given date.

If you don't need the time part: if you're using SQL Server 2008 or later, you can use the DATE type for your column (if you don't need to store the time). Or you can use the same trick while inserting the data on a previous version of SQL Server.

Answer 2

DateTimePicker has a property Value that contains selected date. You should use this date to form a query looking like this:

where somedate >= datetimepicker.Value.Date 
  and somedate < datetimepicker.Value.Date.AddDays(1)

where .Date returns only date portion of DateTime. To prepare where clause, replace where part of SqlCeCommand with

" where conno >= @startDate and conno < @endDate"

Add two parameters to SqlCeCommand

com.Parameters.Add (new SqlCeParameter("@startDate", SqlDbType.DateTime, 
                    textbox1.Value.Date));
com.Parameters.Add (new SqlCeParameter("@endDate", SqlDbType.DateTime, 
                    textbox1.Value.Date.AddDays(1)));

And do similar exercise with cmpsno.

UPDATE: I completely missed last method.

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
    {
        bRDATABindingSource1.Filter = string.Format("dsysdate >= '{0:yyyy-MM-dd}' AND dsysdate < '{1:yyyy-MM-dd}'", dateTimePicker1.Value, dateTimePicker1.Value.AddDays(1));
    }

An attempt at explanation:

As dsysdate has a time component you need to filter an interval starting at midnight and stretching to midnight of next day - this is Value.AddDays(1). Unfortunately I don't know much about filtering BindingSource because I mostly filter at database level, hence first code. You might want to rewrite dateTimePicker1_ValueChanged to retrieve data from database instead of filtering in-memory; it would pay off when working with larger tables because of indexes.

Speaking of that, you might consider structuring your code differently. Such a task is usually done by a method that checks filtering controls, builds query and executes it, thus eliminating duplicated code and enabling you to filter by several criteria at once. Method than gets called when contents of filtering control changes.