Date range in vb.net

Question

I have following code and I can filter data on grid but when I pick same date in 2 datepicker it shows nothing. How can I fix the problem. Any help will be appreciated.

con = New SqlCeConnection(constring)
    con.Open()
    cmd = New SqlCeCommand("SELECT * FROM tblMeter WHERE (Date >= @startDate) AND (Date <  @endDate)", con)
    Dim param1, param2 As SqlCeParameter
    param1 = New SqlCeParameter("@startDate", DateTimePicker1.Value)
    param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value)
    cmd.Parameters.Add(param1)
    cmd.Parameters.Add(param2)
    Dim da As New SqlCeDataAdapter(cmd)
    Dim dt As New DataTable
    da.Fill(dt)
    con.Close()
    DataGridView1.DataSource = dt

Thanks

Answer 1

Change your query to less-than or equal-to your end date.

SELECT * FROM tblMeter WHERE (Date >= @startDate) AND (Date <= @endDate)

Answer 2

It's your logic:

If date is 2001 and you input 2001:

2001 >= 2001 - check
2001 < 2001 - Nope

Answer 3

Try changing the exclusive

(Date < @endDate)

to the inclusive

(Date <= @endDate)

Answer 4

param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value.AddDays(1))

Answer 5

That's because @endDate implies the time 00:00, to include the whole day add the time 23:59:59. Or add 1 to @endDate.

Answer 6

Remember that Sql Server interprets a date like this: 2010-06-23
as a date like this: 2010-06-23 00:00:00.000.

In other words, even if you use >= and <= to check the range at both ends you're still only giving yourself a one millisecond timespan. Sometimes you get away with that if all the dates in the column have no time component, but it's rarely what you really want. Instead, you need to add one day to the end of your range so that your filter includes the entire day, and not just the first millisecond.