CODEWITHSUNDEEP
vb.nett-sqlsql-server-ce
Hakan
Hakan

Reputation: 141

Date range in vb.net

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

Upvotes: 2

Views: 2443

Answers (7)

giannis.epp
giannis.epp

Reputation: 1519

You may use:

param1 = New SqlCeParameter("@startDate", DateTimePicker1.Value.ToShortDateString)
param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value.AddMinutes(1))

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

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.

Upvotes: 2

invert
invert

Reputation: 2076

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.

Upvotes: 1

DCNYAM
DCNYAM

Reputation: 12126

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

Upvotes: 3

JohnK813
JohnK813

Reputation: 1124

Try changing the exclusive

(Date < @endDate)

to the inclusive

(Date <= @endDate)

Upvotes: 0

Wayne Werner
Wayne Werner

Reputation: 51807

It's your logic:

If date is 2001 and you input 2001:

2001 >= 2001 - check
2001 < 2001 - Nope

Upvotes: 0

George Johnston
George Johnston

Reputation: 32258

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

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

Upvotes: 0

Related Questions