Get records between date and time

[biff]

Hi,

I have a problem with an MS Access SQL query that does not pull the records I need. Bascially what I need is all records between 4 or 5 days which also fall between 7:00 AM and 10:15 AM. All times before and after the set period must be excluded.

A few of the variations I've tried are as follows:

1.
WHERE logtime >= #01-Feb-2009 07:00:00 AM#
AND logtime <= #04-Feb-2009 10:15:00 AM#

This version does not exclude the records after 10:15 on the 1st,2nd,3rd and 4th of February (). I also cant use BETWEEN Date1 AND Date2 as it also does not return what I need.

2.
WHERE day(logtime ) in (1,2,3,4)
AND MONTH(logtime) = 2
AND YEAR(logtime) = 2009
AND DATEPART('h', logtime) BETWEEN 7 AND 9

Anyone have any tips/suggestions? Thanks

[NeoPa]

Separate out the Date and Time components and check them separately (Use Between for both).

[NeoPa]

As the date part of a DateTime value is integral, and the time part fractional, that can be done something like :

...
WHERE (CDate(Fix([LogTime])) Between #2/1/2009# And #2/4/2009#)
  AND (CDate([LogTime]-Fix([LogTime])) Between #7:00# And #10:15#)
...

NB. Please see Literal DateTimes and Their Delimiters (#) for further explanation about using dates in SQL.

[biff]

Thanks NeoPA, this issue has been resolved. The MS Access version is :

WHERE  (DateValue(LogTime) between #01-Feb-2009# and #04-Feb-2009#
And TimeValue(LogTime) between #07:00:00 AM# and #10:15:00 AM#)

[NeoPa]

Nice :)

I was hunting around for those functions. To be clear, that is the solution I'd have provided if I'd remembered it ;) This is a better solution than mine.