Parameter Query using 2 dates only using one of the dates

[LabProARW]

Accessing an ACCESS database back end.

DateTime searchDate = DateTime.Now.AddDays(0);
DateTime searchDate2 = DateTime.Now.AddDays(-170);

Using OleDbCommand cmd;
cmd = new OleDbCommand("SELECT * FROM tbl_Sample_Login_table WHERE LoginDate BETWEEN @nowDate AND @thenDate);
cmd.Parameters.Add("@nowDate", OleDbType.DBDate).Value = searchDate;
cmd.Parameters.Add("@thenDate", OleDbType.DBDate).Value = searchDate2;
dbConnection.Open();
dataAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();

** The crazy thing is I keep getting results which only account for searchDate2 and ignores my now search date!? Watching the execution my now date shows today... but when the data set loads it uses some other date every time!
Can anyone tell me why only one of the "BETWEEN" dates is actually getting used in the SELECT query?

Thanks for all responses.

 

[jmcilhinney]

The possible reason I have for using

AddDays(0)

was an attempt to get that Now() current search date to work. Now that I have found the problem - maybe you have a useful way of getting the current date instead of

AddDays(0)

. This fragment which is actually

DateTime searchDate = DateTime.Now.AddDays(0);

works fine.

Sure, it's not going to hurt but we generally don't write pointless code just because it doesn't hurt. If you would do this:

int n = 123 + 0;

then why would you add zero days to a DateTime? Neither hurts but neither does anything useful.

 

[Skydiver]

Just like all those people who call .ToString() on a value that is already a string, or who cast an int to an int.

 

[cjard]

As for the issue, I'm not sure why you're arguing. Just do what you were told to do, i.e. switch the dates:

WHERE LoginDate BETWEEN @thenDate AND @nowDate

As you were told, it's supposed to between low value, i.e. earlier date, and high value, i.e. later date.

By the way, I'm not sure that swapping them over in the SQL statement would work. AFAIAA the Access driver does understand @named parameters to be parameters but the actual names are ignored; position is still important. In the OP's code dates are being added to the parameters collection in the wrong order compared to appearance order of parameter placeholders; notionally all @named parameters are converted to ? parameter placeholders and then the regular rules apply

For example, I would expect these to work:

  ... SELECT * FROM table WHERE x BETWEEN @then AND @now

cmd.Parameters.AddWithValue("@then", DateTime.Today.AddDays(-170));
cmd.Parameters.AddWithValue("@now", DateTime.Today);

  ... SELECT * FROM table WHERE x BETWEEN ? AND ?

cmd.Parameters.AddWithValue("@then", DateTime.Today.AddDays(-170));
cmd.Parameters.AddWithValue("@now", DateTime.Today);

  ... SELECT * FROM table WHERE x BETWEEN @foo AND @bar

cmd.Parameters.AddWithValue("@bar", DateTime.Today.AddDays(-170));
cmd.Parameters.AddWithValue("@foo", DateTime.Today);

  ... SELECT * FROM table WHERE x BETWEEN @foo AND @bar

cmd.Parameters.AddWithValue("@baz", DateTime.Today.AddDays(-170));
cmd.Parameters.AddWithValue("@qux", DateTime.Today);

  ... SELECT * FROM table WHERE x BETWEEN @what AND @ever

cmd.Parameters.AddWithValue("@same", DateTime.Today);
cmd.Parameters.AddWithValue("@same2", DateTime.Today); //if you want to put the same value into a query, you can't reuse an existing one, has to be added again as a new

The advantage for the coder in using named parameters is it's easier to edit the parameter values, code side. The sense in making the names equivalent makes tying SQL and code together easy, but there is that lurkign background issue that for access the order still has to be preserved (and repeated values have to have their own parameters)

---

It's been a long time since I used the access ADO components though

 

[LabProARW]

Or better:

var searchDate = DateTime.Now;

Sorry but why is 'var' better than using 'DateTime' in the declaration?

 

[Skydiver]

In this case, it's less verbose. You want to have programmers focused on the intent of the code, not the syntax of the code. The type is obvious (at least for everyone with industry experience), so no need to declare the type when the compiler (and the programmer) can infer the type.

 

[jmcilhinney]

By the way, I'm not sure that swapping them over in the SQL statement would work. AFAIAA the Access driver does understand @named parameters to be parameters but the actual names are ignored; position is still important. In the OP's code dates are being added to the parameters collection in the wrong order compared to appearance order of parameter placeholders; notionally all @named parameters are converted to ? parameter placeholders and then the regular rules apply

True. I guess I was assuming that they would be added to the command in the same order they appeared in the query but I should have stated that explicitly.