We have a table to store the Events.
Table has got a schema like
EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime
So in the front end we want to show the events according to start time and end time. From front end we are passing StartDate and EndDate as parameters to take the value.
So far we have taken like
Select * from tbl_Events where ((startDate BETWEEN @start AND @end) OR (EndDate BETWEEN @start AND @end))
We have got 2 views to show the events -
A month view - which show all events in the month
A day view showing events of that day
Suppose i got an event with StartDate as 23 October 2012 and EndDate as 27 October 2012.
And i am passing
@StartDate as 24 Oct 2012 and
@EndDate as 25 OCt 2012
According to the above query its working in the Month view. But its not working in the day view.
Can anyone please help me on this to modify the query to work in both cases