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.

 

[JohnH]

It should be BETWEEN @lowValue AND @highValue, but you input opposite values as arguments. Today date is the higher value and older date is a lower value. Think of the dates as numeric values where each time you add for example a second the value increases.

 

[LabProARW]

It should be BETWEEN @lowValue AND @highValue, but you input opposite values as arguments. Today date is the higher value and older date is a lower value. Think of the dates as numeric values where each time you add for example a second the value increases.

The then date and now date did not change that my "nowDate" is being ignored or substituted for 4/13/23 (and my system and backend is current to today. Is there anywhere else where that odd date could be getting taken from? For example might there be a copy of an old database back end file being used rom earlier work?

 

[jmcilhinney]

What possible reason could you have for using .AddDays(0)? What do you think that is doing that's useful?

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.

 

[jmcilhinney]

might there be a copy of an old database back end file being used rom earlier work?

How would we know? You have the database in and the application in front of you. You can see what database the application is connecting to. If it's the wrong one, you tell us.

 

[LabProARW]

How would we know? You have the database in and the application in front of you. You can see what database the application is connecting to. If it's the wrong one, you tell us.

I switched the dates as soon as I read JohnH post so I was not arguing.
Problem resolved:
I found that the back end test file that was more current (had data after 4-13-23), was actually located in a sub folder with the same name as the back end test file which was actually being opened by the connection string, which only had data only as recent as 4-13-23. Relocating the more current database file a folder up and now everything works.

 

[LabProARW]

What possible reason could you have for using .AddDays(0)? What do you think that is doing that's useful?

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.

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.

 

[Skydiver]

But this would have worked more succinctly:

DateTime searchDate = DateTime.Now;

 

[Skydiver]

Or better:

var searchDate = DateTime.Now;

 

[Skydiver]

Or if you need to potentially account for what time your code is running, but you just need today, then you should use:

var searchDate = DateTime.Today;

which will give you a DateTime with the time portion set to midnight.

 

[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.