Between Dates Not Working Properly

[GLT]

Hi,

Can anyone advise why this statement does not pick up todays date?

Between Date()-1 And Date()

The above statement will select everything for the 27th, but not the 28th..

Any help is always greatly appreciated...

Cheers,
GLT.

 

[KARL DEWEY]

The above statement will select everything for the 27th, but not the 28th..
Where are you, my calendar says it is the 27th so it will not pull records
for the 28th.

 

[Rick Brandt]

Hi,

Can anyone advise why this statement does not pick up todays date?

Between Date()-1 And Date()

The above statement will select everything for the 27th, but not the
28th..

Any help is always greatly appreciated...

Do your dates alos have non-midnight times? Your query is actually...

Between yesterday at exactly midnight and today at exactly midnight.

That will not include any date values for today that are past midnight
(which could be all of them).

 

[John W. Vinson]

Hi,

Can anyone advise why this statement does not pick up todays date?

Between Date()-1 And Date()

The above statement will select everything for the 27th, but not the 28th..

This code will pull everything from midnight at the beginning of yesterday to
midnight at the beginning of today - that is, only YESTERDAY'S data.

The way Access stores dates is as a double float count of days and fractions
of a day since midnight, December 30, 1899. If the fractional part of the
number is 0 it corresponds to midnight at the beginning of that day. Date() is
(at the time I'm doing this) 39505.0000000000000, corresponding to
#02/27/2008 00:00:00#. If you have a record in your table for today's date at
9:00 AM, it will be stored in your table as 39505.375000000000 - which is NOT
between 39504 and 30505, hence it will not be retrieved.

If you want today's data use

BETWEEN Date() AND Date() + 1

or

BETWEEN Date() AND Now() if you want "so far today" data.

 

[Ken Sheridan]

When defining a date range by means of a BETWEEN….AND operation, any rows
containing date/time values on the last day of the range where the time of
day element is greater than zero will not be returned (for the reason the
other respondents have explained). Its very easy for dates to be given a
non-zero time of day inadvertently unless you take steps in the table
definition to prevent this; the use of the Now() function to insert a value
is a common culprit.

You can easily return rows which included dates on the final day of the
range even if the values include a non-zero time of day by defining the range
as follows:

SELECT *
FROM [YourTable]
WHERE [YourDateField] >= DATE()-1
AND [YourDateField] < DATE()+1;

If a date/time value is on or after midnight at the start of yesterday, and
before midnight at the start of tomorrow then the WHERE clause will evaluate
to TRUE and the row will be returned. Midnight is treated by Access as 'zero
hour', i.e. the very first 'moment' of the day, though it might also be
regarded as the very last 'moment' of the day. Joe Celko briefly discusses
this point in connection with the ISO date/time standard (which allows both
notations, 00:00 and 24:00 for midnight) in his book 'Data and Databases –
Concepts in Practice'. If you are interested in the ISO standard take a look
at:


http://www.cl.cam.ac.uk/~mgk25/iso-time.html


Ken Sheridan
Stafford, England

 

[GLT]

Thanks Gents - I have now fixed this problem...

Cheers,
GLT.

 

[James A. Fortune]

When defining a date range by means of a BETWEEN….AND operation, any rows
containing date/time values on the last day of the range where the time of
day element is greater than zero will not be returned (for the reason the
other respondents have explained). Its very easy for dates to be given a
non-zero time of day inadvertently unless you take steps in the table
definition to prevent this; the use of the Now() function to insert a value
is a common culprit.

You can easily return rows which included dates on the final day of the
range even if the values include a non-zero time of day by defining the range
as follows:

SELECT *
FROM [YourTable]
WHERE [YourDateField] >= DATE()-1
AND [YourDateField] < DATE()+1;

If a date/time value is on or after midnight at the start of yesterday, and
before midnight at the start of tomorrow then the WHERE clause will evaluate
to TRUE and the row will be returned. Midnight is treated by Access as 'zero
hour', i.e. the very first 'moment' of the day, though it might also be
regarded as the very last 'moment' of the day. Joe Celko briefly discusses
this point in connection with the ISO date/time standard (which allows both
notations, 00:00 and 24:00 for midnight) in his book 'Data and Databases –
Concepts in Practice'. If you are interested in the ISO standard take a look
at:


http://www.cl.cam.ac.uk/~mgk25/iso-time.html


Ken Sheridan
Stafford, England

Your advice is good except that you are, in your own words, getting too
chummy with the implementation. DateAdd would be more portable in the
very unlikely event that Microsoft implements dates in Access as
something other than Double, assuming they update the DateAdd function
at the same time.

See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/52d7ca1b2e8a2b6a

and

http://groups.google.com/group/microsoft.public.access/msg/002645d0ba1cea96

DateAdd is also available in T-SQL so it doesn't affect portability:

http://msdn2.microsoft.com/en-us/library/ms186819.aspx

Taking this to the extreme, even date comparisons in SQL should probably
use date comparision functions instead of, say <.

BTW, after reading most of your posts here I admit that my first
impression of you in CDMA was inaccurate. However, I still use
functions to calculate holidays as opposed to the more Celkoesque
holiday table .

James A. Fortune
(e-mail address removed)