Filtering with StartTime and Endtime problem

Hello,

I have a problem with the following task:

The user is prompted to specify a starttime like 02 and a endtime 04.

The user for example wants to search all events between 2 am and 10 am regardless of the date.

I use the following SQL ti filter:

Where (DATEPART(hour, Event.EventDateTime) BETWEEN @.StartHour AND @.EndHour)

This works fine as long starhour is smaller than endhour.

When i want to filter Events beteween 23 (11 pm.) and 02 (2 am) i get no results. I have to make a search possible for a time span that is over midnight.

Anyone who has some ideas?

Thank you i advance!

Hello Luskan,

Try this:

select *

from Table1

where 1 =

case

when @.StartHour > @.EndHour and (datepart(hour, Event.EventDateTime) >= @.StartHour or datepart(hour, Event.EventDateTime) <= @.EndHour) then 1

when @.StartHour <= @.EndHour and datepart(hour, Event.EventDateTime) between @.StartHour and @.EndHour then 1

end

Hope this helps.

Jarret