Query to Select Between Two Times of Day

Question

I am trying to build a query that will select based on a DateTime column SyncDate.

As some background, the table I query from gets imports daily in the thousands at the exact same time everyday. I want to find the entries that did not import at the "regular" time of day.

Therefore, I want to query where the time in the DateTime column is between two times: lets say 14:00-14:30 (2-230) on ANY day/month/year period.

SELECT * FROM MyTable
WHERE DatePart(SyncDate, ..?) BETWEEN (14:00..?) and (14:30..?)

The DatePart function seems to be what I need, but I don't understand how to apply it to this situation. Your help is much appreciated oh great queriers.

EDIT: I was mistaken, I am running SQL-Server-2005 as my backend. Sorry!

Answer 1

Since you're on SQL Server 2008, you can use the new TIME datatype:

SELECT * FROM MyTable
WHERE CAST(SyncDate AS TIME) BETWEEN '14:00' and '14:30'

If your backend isn't 2008 yet :-) then you'd need something like:

SELECT * FROM MyTable
WHERE DATEPART(HOUR, SyncDate) = 14 AND DATEPART(MINUTE, SyncDate) BETWEEN 0 AND 30

to check for 14:00-14:30 hours.

Answer 2

How about this....

select * from MyTable where
cast(cast(datepart(HH,SyncDate) as char(2)) + cast(datepart(HH,SyncDate) as char(2)) as int) between 1400 and 1430