Query on "TIME" only part of DateTime

[Smitty020]

Okay, I have a small problem.

I have one Date field in a table called Transaction.

From the GUI, the user has 4 options to interrogate this field. There are "start date", "end date", "start time", and "end time" datetime pickers. The user should be able to retrieve data for any set of days, and within those days, any specific time period. (i.e. They may want to see all of the transactions between Oct 9 and Oct 16 that occurred within the hours of 2:00PM and 3:00PM.

Could someone help out with the SQL. The "date" portion is not the issue, it's the "Time" portion that I am having a problem with.

Thanks in advance.

-D

 

[sparkbyte]

Here is some code one of my co-workers did for me. Hopefully it will help you.

DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated

DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = '2006-09-27 00:00:00.000' --FROM SecurityEvents_TimeGenerated

DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = '2006-09-27 11:59:59.000' --FROM SecurityEvents_TimeGenerated

--DECLARE @TimeGeneratedEnd datetime
--SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated

--SELECT
--BankName = CASE WHEN b.BankName IS NULL THEN 'All Banks' ELSE b.BankName END,
--a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
SELECT Count(*)
FROM SecurityEvents_Tmp a
LEFT JOIN BankIDList b on a.BankNumber = b.BankNumber
LEFT JOIN EmailToList c on b.BankNumber = c.BankNumber
JOIN EventsToLog d on a.EventID = d.EventID
WHERE
b.banknumber = '160' --in (select banknumber from bankidlist)
AND a.TimeGenerated BETWEEN @TimeGenerated  AND  @TimeGeneratedEnd
--ORDER BY b.BankName,a.EventID,a.TimeGenerated

--SELECT Count(all 025)

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com