asked on

Query records using only time as a filter

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

ashraf882

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PoornimaKolapalli

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)

Sharath S

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.

ashraf882

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

SOLUTION

PoornimaKolapalli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Programmers

ASKER

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