Re: Search Date and Time

Tech-Archive recommends: Fix windows errors by optimizing your registry

OK, you've got me there :-)
Douglas J. Steele wrote:
I would think that if the developer is sophisticated enough to have
created indexes based on expected query paths, he/she would also be
sophisticated enough to realize that the date and time should be
stored in a single field. <g>


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OHe8fwc8IHA.3736@xxxxxxxxxxxxxxxxxxxxxxx
It may be easier but it one needs to use an index to improve
performance, this approach negates any index use.

Douglas J. Steele wrote:
While that will work in this particular example, it's far easier
just to add the date and time together:

WHERE (DateField + TimeField) BETWEEN #7/23/2008 6:00:00 PM# and
#7/24/2008 6:00:00 AM#


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:uJmjaeW8IHA.2348@xxxxxxxxxxxxxxxxxxxxxxx
Bob Barrows [MVP] wrote:
No. That is why we usually recommend storing both date and time
in a single Date/Time field.

If you have some reason not to be able to comfine them, you will
need to use compound criteria for both fields. This will be
easier to demonstrate using sql, so switch your query to SQL View
using the View menu, or the toolbar button, or the right-click context
menu.
Assuming DateField and TimeField are both Date/Time fields, the
WHERE clause should look something like this:
WHERE (DateField = #7/23/2008# AND timeField >= 6:00:00 PM#)
OR
(DateField = #7/24/2008# AND timeField <= 6:00:00 AM#)


Typo ... it should be:

WHERE (DateField = #7/23/2008# AND timeField >= #6:00:00 PM#)
OR
(DateField = #7/24/2008# AND timeField <= #6:00:00 AM#)



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap
so I don't check it very often. If you must reply off-line, then
remove the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Quantcast