Easy SQL Query Question

[mlocurci]

I need to only pull records from 6:30AM till 10:30PM and on a certain date. How do I modify thia where clause to do that???

WHERE (((r.date_time) Between #9/1/2002# And #9/30/2002 22:30:0#) AND ((r.ct_id)=1))

Thanks!

-Michael

 

[mlocurci]

It still hates me:

WHERE (((r.date_time) '9/1/02' and '9/15/02 23:59:29'
and datepart(hh,date) between 7 and 23
) AND ((r.ct_id)=1))
GROUP BY LTrim(Str(DatePart(&quot;m&quot;,r.date_time)))+IIf(DatePart(&quot;d&quot;,r.date_time)<10,&quot;/0&quot;,&quot;/&quot+LTrim(Str(DatePart(&quot;d&quot;,r.date_time)))+&quot;/&quot;+LTrim(Str(DatePart(&quot;yyyy&quot;,r.date_time)))
HAVING (((Int(Sum([r].[in_time]+[r].[acw_time])/100))<>0))
ORDER BY LTrim(Str(DatePart(&quot;m&quot;,r.date_time)))+IIf(DatePart(&quot;d&quot;,r.date_time)<10,&quot;/0&quot;,&quot;/&quot+LTrim(Str(DatePart(&quot;d&quot;,r.date_time)))+&quot;/&quot;+LTrim(Str(DatePart(&quot;yyyy&quot;,r.date_time)));

 

[skicamel]

WHERE (((r.date_time) BETWEEN '9/1/02' and '9/15/02 23:59:29'
and datepart(hh,date) between 7 and 23

 

[SQLBill]

mlocurci,

I suggest that you also post your question in the MS ACCESS forum since that is what you are using.

-SQLBill

 

[malexanian]

Instead of these inefficient dateparts, take advantage of the fact that dates in Access are stored in terms of days from 1/1/1900.

WHERE
r.date_time >= #9/1/2002# AND
r.date_time < #9/16/2002# AND
fpart(r.date_time)
Between #1/1/1900 07:00:00# And #1/1/1900 23:00:00#

The first part does the date, the second part does the time. Note that fpart is VBA, not Jet SQL, so the engine cannot optimize for it. The Jet SQL engine runs the first criteria, returns the result to Access, and Access applies the second criteria.

This is still better than the dateparts, since datepart is VBA. The Jet SQL engine returns the entire table, and Access applies the datepart criteria. This is extremely slow because Access has do all of the processing. Jet SQL processing is always faster than Access.