By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

Get records between date and time

P: 2
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
Feb 16 '09 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,419
Separate out the Date and Time components and check them separately (Use Between for both).
Feb 16 '09 #2

NeoPa
Expert Mod 15k+
P: 31,419
As the date part of a DateTime value is integral, and the time part fractional, that can be done something like :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE (CDate(Fix([LogTime])) Between #2/1/2009# And #2/4/2009#)
  3.   AND (CDate([LogTime]-Fix([LogTime])) Between #7:00# And #10:15#)
  4. ...
NB. Please see Literal DateTimes and Their Delimiters (#) for further explanation about using dates in SQL.
Feb 16 '09 #3

P: 2
Thanks NeoPA, this issue has been resolved. The MS Access version is :
Expand|Select|Wrap|Line Numbers
  1. WHERE  (DateValue(LogTime) between #01-Feb-2009# and #04-Feb-2009#
  2. And TimeValue(LogTime) between #07:00:00 AM# and #10:15:00 AM#)
Feb 17 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 17 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.