Date/Time

[Gene]

There was a post that said "Bear in mind that you cannot store a date without
a time - a default time of 00:00:00 is assumed." (See Pete, 10/27/06)
I am having a lot of trouble because of the "time" that is inexorably
embedded in the date. Can someone tell me if the default time of 00:00:00 is
the first moment of the day in question or the last moment of the day? For
instance if I want to see all entries that are dated after December 31, 2007.
In a querry, if I say show me all the entries that are >
dateValue(12/31/07), and one of the entries is 12/31/07 1:00, will it be
shown? If the default value of 0:00:00 is the beginning of the day in
question, then I would want to use >datevalue(1/1/08). Any words of wisdom
would be appreciated.

 

[Marshall Barton]

There was a post that said "Bear in mind that you cannot store a date without
a time - a default time of 00:00:00 is assumed." (See Pete, 10/27/06)
I am having a lot of trouble because of the "time" that is inexorably
embedded in the date. Can someone tell me if the default time of 00:00:00 is
the first moment of the day in question or the last moment of the day? For
instance if I want to see all entries that are dated after December 31, 2007.
In a querry, if I say show me all the entries that are >
dateValue(12/31/07), and one of the entries is 12/31/07 1:00, will it be
shown? If the default value of 0:00:00 is the beginning of the day in
question, then I would want to use >datevalue(1/1/08). Any words of wisdom
would be appreciated.

0:00:00 is midbight on the day in the date part (i.e.
beginning of the day).

 

[Gene]

Thank you, I was afraid of that. Given that, is there anyway for me to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?

Gene

 

[Douglas J. Steele]

If you're using Now, you've already got the current time in there.

I'm not sure whether you're trying to get 11:59:59 PM of the previous day,
or 11:59:59 PM of that day.

For the latter, try:

vDate=DateAdd("m", -1, Date) + TimeSerial(11, 59, 59)

For the former, try:

vDate=DateAdd("d", -1, DateAdd("m", -1, Date)) + TimeSerial(11, 59, 59)

 

[Marshall Barton]

You should also consider:

The DateValue function discards the time part of a date/time
value.

Unlike the Now function, the Date function returns 0:00:00
in the time part.

You can compare a date/time value using
vdate < DateAdd("d", 1, Date())

 

[John W. Vinson]

Thank you, I was afraid of that. Given that, is there anyway for me to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?

The criterion I'll ususally use to get all records on a particular date is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Gene]

Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a particular
day, and if the time is embedded, then the > function finds events on the
same day, but at a later time. If I add a day, then it won't find the events
that occured on that day BEFORE the embedded time. Either way, I have to fix
the beginning or the end of the day and write the code to fit. Now that I
know that the 00:00:00 is the beginning of the day, I can embed the 11:59:59
PM to the date and know that anything greater than that will be the next day.
This problem happened to me, and that is why I needed the help. The code
has been working for years, but just failed because of the overlap in the
TIME problem.
Gene

Thank you, I was afraid of that. Given that, is there anyway for me to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?

The criterion I'll ususally use to get all records on a particular date is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Douglas J. Steele]

Might your problem be caused by the fact that you're using the Now function,
not the Date function?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a particular
day, and if the time is embedded, then the > function finds events on the
same day, but at a later time. If I add a day, then it won't find the
events
that occured on that day BEFORE the embedded time. Either way, I have to
fix
the beginning or the end of the day and write the code to fit. Now that I
know that the 00:00:00 is the beginning of the day, I can embed the
11:59:59
PM to the date and know that anything greater than that will be the next
day.
This problem happened to me, and that is why I needed the help. The code
has been working for years, but just failed because of the overlap in the
TIME problem.
Gene

Thank you, I was afraid of that. Given that, is there anyway for me to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?

The criterion I'll ususally use to get all records on a particular date
is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Gene]

Absolutely! If I knew then what I have learned over time from writing VBA
code and this Discussion Group's help, I would have gone a different route.
At this time, however, changing all the interwoven code seems more dangerous
than making a band-aide fix for this rare occurrence.
In any event, the problem is that the time is embedded with the date, so
whether it defaults to the beginning or end of the day, or fixes a current
time, it has to be taken into account, even though it is irrelevant to my
needs.
Gene

Might your problem be caused by the fact that you're using the Now function,
not the Date function?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a particular
day, and if the time is embedded, then the > function finds events on the
same day, but at a later time. If I add a day, then it won't find the
events
that occured on that day BEFORE the embedded time. Either way, I have to
fix
the beginning or the end of the day and write the code to fit. Now that I
know that the 00:00:00 is the beginning of the day, I can embed the
11:59:59
PM to the date and know that anything greater than that will be the next
day.
This problem happened to me, and that is why I needed the help. The code
has been working for years, but just failed because of the overlap in the
TIME problem.
Gene

Thank you, I was afraid of that. Given that, is there anyway for me to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?


The criterion I'll ususally use to get all records on a particular date
is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Douglas J. Steele]

It's pretty trivial to ignore the time, either permanently or just in a
query.

To do it permanently, you can write an Update query:

UPDATE MyTable SET MyDateField = DateValue(MyDateField)

To do it temporarily in a query, just wrap the DateValue function around
your date field(s).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Absolutely! If I knew then what I have learned over time from writing VBA
code and this Discussion Group's help, I would have gone a different
route.
At this time, however, changing all the interwoven code seems more
dangerous
than making a band-aide fix for this rare occurrence.
In any event, the problem is that the time is embedded with the date, so
whether it defaults to the beginning or end of the day, or fixes a current
time, it has to be taken into account, even though it is irrelevant to my
needs.
Gene

Might your problem be caused by the fact that you're using the Now
function,
not the Date function?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a
particular
day, and if the time is embedded, then the > function finds events on
the
same day, but at a later time. If I add a day, then it won't find the
events
that occured on that day BEFORE the embedded time. Either way, I have
to
fix
the beginning or the end of the day and write the code to fit. Now
that I
know that the 00:00:00 is the beginning of the day, I can embed the
11:59:59
PM to the date and know that anything greater than that will be the
next
day.
This problem happened to me, and that is why I needed the help. The
code
has been working for years, but just failed because of the overlap in
the
TIME problem.
Gene

:

On Mon, 3 Mar 2008 14:17:08 -0800, Gene
<[email protected]>
wrote:

Thank you, I was afraid of that. Given that, is there anyway for me
to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?


The criterion I'll ususally use to get all records on a particular
date
is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Gene]

This whole thread started out because the DaveValue does not "ignore" the
time, it just sets it to 00:00:00 (or the beginning of the day). I thought
the time was trivial until I hit this very rare exception, but I am glad I
found it rather than a client. As you note, it isn't hard to work around,
but you must take into account the time value regardless of what funcion you
use.
Gene

It's pretty trivial to ignore the time, either permanently or just in a
query.

To do it permanently, you can write an Update query:

UPDATE MyTable SET MyDateField = DateValue(MyDateField)

To do it temporarily in a query, just wrap the DateValue function around
your date field(s).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Absolutely! If I knew then what I have learned over time from writing VBA
code and this Discussion Group's help, I would have gone a different
route.
At this time, however, changing all the interwoven code seems more
dangerous
than making a band-aide fix for this rare occurrence.
In any event, the problem is that the time is embedded with the date, so
whether it defaults to the beginning or end of the day, or fixes a current
time, it has to be taken into account, even though it is irrelevant to my
needs.
Gene

Might your problem be caused by the fact that you're using the Now
function,
not the Date function?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a
particular
day, and if the time is embedded, then the > function finds events on
the
same day, but at a later time. If I add a day, then it won't find the
events
that occured on that day BEFORE the embedded time. Either way, I have
to
fix
the beginning or the end of the day and write the code to fit. Now
that I
know that the 00:00:00 is the beginning of the day, I can embed the
11:59:59
PM to the date and know that anything greater than that will be the
next
day.
This problem happened to me, and that is why I needed the help. The
code
has been working for years, but just failed because of the overlap in
the
TIME problem.
Gene

:

On Mon, 3 Mar 2008 14:17:08 -0800, Gene
<[email protected]>
wrote:

Thank you, I was afraid of that. Given that, is there anyway for me
to
manually embed 11:59:59 PM into a date that I am constructing with:
vDate=DateAdd("m", -1, Now)?


The criterion I'll ususally use to get all records on a particular
date
is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Douglas J. Steele]

Sorry, I haven't read the thread from its beginning, but I fail to see how
having 00:00:00 as the time associated with a date-only value can be an
issue.

If you've got data that includes date and time, and all you want to do is
compare it to a date, your Where clause could be something like:

WHERE DateValue([MyDateTimeValue]) = [Input date in mm/dd/yyyy format]

although it would be far more efficient to use

WHERE [MyDateTimeValue] BETWEEN [Input date in mm/dd/yyyy format] AND
DateAdd("d", 1, [Input date in mm/dd/yyyy format])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This whole thread started out because the DaveValue does not "ignore" the
time, it just sets it to 00:00:00 (or the beginning of the day). I
thought
the time was trivial until I hit this very rare exception, but I am glad I
found it rather than a client. As you note, it isn't hard to work around,
but you must take into account the time value regardless of what funcion
you
use.
Gene

It's pretty trivial to ignore the time, either permanently or just in a
query.

To do it permanently, you can write an Update query:

UPDATE MyTable SET MyDateField = DateValue(MyDateField)

To do it temporarily in a query, just wrap the DateValue function around
your date field(s).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Absolutely! If I knew then what I have learned over time from writing
VBA
code and this Discussion Group's help, I would have gone a different
route.
At this time, however, changing all the interwoven code seems more
dangerous
than making a band-aide fix for this rare occurrence.
In any event, the problem is that the time is embedded with the date,
so
whether it defaults to the beginning or end of the day, or fixes a
current
time, it has to be taken into account, even though it is irrelevant to
my
needs.
Gene

:

Might your problem be caused by the fact that you're using the Now
function,
not the Date function?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you all, as usual very helpful.
John,
I am being precise because I need to find all the dates AFTER a
particular
day, and if the time is embedded, then the > function finds events
on
the
same day, but at a later time. If I add a day, then it won't find
the
events
that occured on that day BEFORE the embedded time. Either way, I
have
to
fix
the beginning or the end of the day and write the code to fit. Now
that I
know that the 00:00:00 is the beginning of the day, I can embed the
11:59:59
PM to the date and know that anything greater than that will be the
next
day.
This problem happened to me, and that is why I needed the help. The
code
has been working for years, but just failed because of the overlap
in
the
TIME problem.
Gene

:

On Mon, 3 Mar 2008 14:17:08 -0800, Gene
<[email protected]>
wrote:

Thank you, I was afraid of that. Given that, is there anyway for
me
to
manually embed 11:59:59 PM into a date that I am constructing
with:
vDate=DateAdd("m", -1, Now)?


The criterion I'll ususally use to get all records on a particular
date
is

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Why do you care about one second before midnight???

 

[Marshall Barton]

Sorry, I haven't read the thread from its beginning, but I fail to see how
having 00:00:00 as the time associated with a date-only value can be an
issue.

If you've got data that includes date and time, and all you want to do is
compare it to a date, your Where clause could be something like:

WHERE DateValue([MyDateTimeValue]) = [Input date in mm/dd/yyyy format]

although it would be far more efficient to use

WHERE [MyDateTimeValue] BETWEEN [Input date in mm/dd/yyyy format] AND
DateAdd("d", 1, [Input date in mm/dd/yyyy format])

To avoud matching midnight on the next day, I think that
last one should be:

WHERE [MyDateTimeValue] >=[Input date in mm/dd/yyyy format]
AND [MyDateTimeValue] < DateAdd("d", 1, [Input date in
mm/dd/yyyy format])

 

[Douglas J. Steele]

Sorry, I haven't read the thread from its beginning, but I fail to see how
having 00:00:00 as the time associated with a date-only value can be an
issue.

If you've got data that includes date and time, and all you want to do is
compare it to a date, your Where clause could be something like:

WHERE DateValue([MyDateTimeValue]) = [Input date in mm/dd/yyyy format]

although it would be far more efficient to use

WHERE [MyDateTimeValue] BETWEEN [Input date in mm/dd/yyyy format] AND
DateAdd("d", 1, [Input date in mm/dd/yyyy format])

To avoud matching midnight on the next day, I think that
last one should be:

WHERE [MyDateTimeValue] >=[Input date in mm/dd/yyyy format]
AND [MyDateTimeValue] < DateAdd("d", 1, [Input date in
mm/dd/yyyy format])

Yeah, you're right, Marsh. You can usually get away with being sloppy like I
was when the field's populated using the Now function, since the odds of a
record having exacting midnight as its time are very low, but why take that
chance?