Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
it worked!! thanks a lot.. –  mahesh Oct 11 '12 at 8:23

Using datetime data types with between can be tricky, you can get caught out by the time part of the date.

To confirm this, try:

Select * from tbl_Events 
  where ((startDate >= @start AND startDate <= @end) OR (EndDate >= @start AND EndDate <= @end)) 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.