Access BETWEEN statement seems faulty in VB.NET

[Siv]

Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??

 

[Chris Petchey]

And vb.net syntax in general is intuitive?
It may seem "counter intuitive" to you, but it makes good sense if you
really take the time to stop and think about it. You asked for invoices
between 1/8/2005 00:00:00 and 31/8/2005 00:00:00
If the query worked "intuitively" what would I do if I wanted to include
all invoices put on at midnight, but no further?

Perhaps the flaw lies in your invoice date. If your invoicedate field
data didn't carry the time element your query would give the result you
expect. Do you really need to know the time of the invoice?
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.
Instead consider InvoiceDate>=Startdate and InvoiceDate<(EndDate+1)

Gadzukes, so many choices!

 

[Larry Lard]

Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.

That's what you said, so you can hardly blame the system for taking
that to be what you meant.

Is 3.5 'between' 1 and 3 ? No.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6,

Youre sureness is misplaced. Check it yourself and see!

I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??

This root of your difficulty is, I would guess, treating DateTime
fields in Access as if they were Date fields (note that the latter do
not actually exist). The field you name 'InvoiceDate' actually holds a
date-and-time, and should be treated as such. Either be sure to only
store pure dates if you don't care about times; or explicitly drop the
time part of date-and-time values when you don't care about it.

 

[Larry Lard]

Chris Petchey wrote:
[snippage]

Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.

Why do you say this?

 

[Al Reid]

Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.

I assume this comment was made in jest.

Between is a perfectly legitimate and useful SQL predicate and is included in the current ANSI SQL standard. Because one doesn't
know how to use something does not make it evil.

 

[Siv]

Larry,

Thanks for the comments, I use the Access Date/Time field as it is the field
of choice for dates, but as you rightly surmise I am only interested in the
date.
The reason the issue has come up and caught me out is that in nearly all
locations in the software, I have used just the date, and the query does
work for those. I have slipped up somewhere and must be inserting the date
and time in some of my records. I think the routine that allows the user to
create a credit note must be where it is as the only items that are slipping
out of the query are credit notes.

At least I now know why I have always assumed VB6 was doing it differently
to dot net. It must be because I have always stored just the date and
ignored the time part and this is the first time I have noticed it??

 

[Siv]

Chris,
Thanks for the "ticking off". My reason for saying it is counter intuitive is that in the definition of the between statement I looked up at

http://www.techonthenet.com/sql/between.php

in the date section "Example 2", they say:

"Example #2 - Dates
You can also use the BETWEEN function with dates.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).
It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');"

The bit that makes the way it works seem counter intuitive is that word in brackets (inclusive), to me inclusive would mean from the start of the period to the end of the period and by end I mean the end of the last date of the between statement. If you say the statement is "inclusive" then only going to 31/08/2005 00:00:00 if I don't specifically quote a time seems contrary to the statement "inclusive".

Now that I am aware why it has behaved differently (because some bit of my code that deals with credit notes is using a date and a time and storing that in the record has made me fall foul of this where everywhere else I do not use the time), I can work round it.

Please note I have been using between for years, and have always believed "it did what it said on the tin" because I have always kept the time out of the equation (without realising that was important). The records that were being missed by the between statement were in fact the only ones that were in the table containing a time element. This is down to a blunder on my part in the Credit Note code, but as I often say to other developers, you often learn more from your mistakes than always getting it right.

 

[Siv]

Al,
Thanks for sticking up for me/the between statement. I have always found it
useful.

As you will see in my response to Chris's post, the misunderstanding about
how it really works has caught me out because I have accidentally added some
code that is storing the time as well as the date in the table. After
reading the comments here I checked the table and sure enough, the records
that were being missed out of the query all had a time element whereas the
ones that worked did not.

 

[Cor Ligthert [MVP]]

Siv,

In addition to the others,

SQL statements are not a part of VB.Net itself.

It is just given to the database to execute conform its standards and return
a resultset.

By the way, the method in Adonet is to use parameters.

http://www.windowsformsdatagridhelp.com/default.aspx

I hope this helps,

Cor

 

[Siv]

Cor,
You mention that it is best to use parameters, do you mean parameter
queries? If so could you explain why that is so?
I tend to create SQL strings and execute them, that way there is nothing in
the database itself that a user might delete and thus break the program
(note: I am building single user applications on non networked PCs so they
are just running a local copy of an MS Access database and a VB.NET
application) so that the setup is easy. All the users of the application
are completely non-technical so ease of setup is paramount.

 

[Cor Ligthert [MVP]]

Siv,

There is one simple answer.

If you use the parameters, than especially with dates, the conversion
between SQL datetime types and the Net datetime is done in VBNet.

Especially because there are a lot of cultures, than by instance a datetime
typed in the US as 08/31/2005 is for the system the same as that you or I
type 31/08/2005, while if it is a time, than you can use your 'pm', while I
use a 24 hour clock and that all with the same program, just depending on
the used culture settings.

(With SQL server it prevent as well for hackers, however you are talking
about Jet.)

It change for the rest nothing, you can use it with dynamic SQL (sqlstrings)
and with stored procedures.

I hope this gives some idea's

Cor

 

[Chris Petchey]

Indeed it was a jest. In my haste I forgot the obligatory smiley symbol.
It is Cor Ligthert that is included for backward compatibility with the
abacus

 

[Cor Ligthert [MVP]]

Chris,

I have always had my interest in the abacus. A pity is that I never worked
with that, can you enlighten me a little bit about that backwards
compatibility you are talking about.

I get the idea that you are an expert in using the abacus. (I have heard
that some people still use that to check the results of the computer so
maybe you are one of those).

Cor

 

[Chris Petchey]

For sure, how else do you check?

Chris,

I have always had my interest in the abacus. A pity is that I never worked
with that, can you enlighten me a little bit about that backwards
compatibility you are talking about.

I get the idea that you are an expert in using the abacus. (I have heard
that some people still use that to check the results of the computer so
maybe you are one of those).

Cor

 

[Siv]

Cor,
Thanks for the clarification, this will be useful in many projects.