Query records using only time as a filter

Programmers
Programmers used Ask the Experts™
on
I am trying to write a query that will look for records that match a particilar time (ie 11:30am and 2.30pm) during a particiluar date period (ie 1 dec 2009 and 31 dec 2009).

I can get the records for the date period in question but not quite sure how to filter that again to the time period. The date and time are in the same database field. My query so far is included.

I'm sure this is quite easy but unfortunately it escapes me at the moment. Thanks for the help in advance. BTW I'm using SQL Server 2005.
SELECT
Item.MerchantId,     
Item.CardNumber
FROM Item 
WHERE Item.StartDateTime BETWEEN '1 dec 2009' AND '1 jan 2010'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This may be helpful-http://www.sql-server-performance.com/articles/dev/date_time_search_p1.aspx
SELECT
Item.MerchantId,     
Item.CardNumber
FROM Item 
WHERE Item.StartDateTime BETWEEN '1 dec 2009' AND '1 jan 2010' and Item.StartDateTime BETWEEN '1:00 PM' and '3:00 PM'

Open in new window

Data Engineer
Commented:
try this.
SELECT
Item.MerchantId,     
Item.CardNumber
FROM Item 
WHERE (Item.StartDateTime BETWEEN '1 dec 2009' AND '1 jan 2010')
and (convert(varchar(20),Item.StartDateTime,14) between '11:30:00' and '14:30:00')

Open in new window

Hi
This may Helpful to you...

SELECT Item.MerchantId,    
Item.CardNumber FROM Item
WHERE Item.StartDateTime
 BETWEEN
convert(datetime,'1 dec 2009 11:30:00',120)
 AND
convert(datetime,'1 jan 2010 14:30:00',120)
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

SharathData Engineer

Commented:
Poornima - I don't think your query will work for this requirement. The asker is looking for only those dates between 11:30 AM and 2:30PM and between 1 Dec, 09 and 1 Jan,10.

Your query will pick all the records between 1 Dec,09 11:30AM to 1 Jan,10 2:30PM. For example if there is a date like 15 Dec,09 4:00PM. The asker does not want this record but your query display this record as well.
Yes, Sharath_123 is corrrect. Here is the solution-

Select 
Item.MerchantId,     
Item.CardNumber
FROM Item 
WHERE Item.StartDateTime BETWEEN '2009-01-01 11:30:00' And '2010-01-01 14:30:00';

Open in new window

Hi sharath
Thanks for your feed back
yes you r right i didn't observe that;
try this one :

SELECT
Item.MerchantId,    
Item.CardNumber
FROM Item
WHERE Item.StartDateTime BETWEEN '1 dec 2009' AND '1 jan 2010'
and  (CONVERT(VARCHAR(8),Item.StartDateTime, 108) BETWEEN '11:30:00' AND '14:30:00')

Author

Commented:
Thanks for the input, just couldn't get the formatting right.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial