Sql-server – Select query according to date

selectsql server

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 –

  1. A month view – which show all events in the month

  2. 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

Best Answer

You simply need to think about the date range slightly differently. An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:

Select * from tbl_Events
where StartDate <= @end
    AND EndDate >= @start;

For example:

CREATE TABLE MyTestDates
(
    MyTestDateID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT PK_MyTestDates IDENTITY(1,1)
    , StartDate DATETIME
    , EndDate DATETIME
    , EventName NVARCHAR(255)
);

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-01-30', 'Test Event 1');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-02-28', 'Test Event 2');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2011-01-01', '2012-03-22', 'Test Event 3');

DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;

SET @FromDate = '2012-01-01';
SET @ToDate = '2012-02-01'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2012-01-28';
SET @ToDate = '2012-01-29'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2010-01-01';
SET @ToDate = '2012-05-15'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

DROP TABLE MyTestDates;

The three SELECT statements return all events in this example.

Related Question