SQL query confusion for startdate and enddate

select, select-query, sql, sql-server

Solution

declare @T table
(
  Name char(1),
  Startdate datetime,
  Enddate datetime
)

insert into @T values
('a',     '20120701',  '20130630'), 
('b',     '20110501',  '20120430'), 
('c',     '20100101',  '20131231'), 
('d',     '20130101',  '20141231'), 
('e',     '20110701',  '20120630')

declare @StartDate datetime = '20120501'
declare @EndDate datetime = '20121031'

select Name
from @T 
where Startdate < @EndDate and
      Enddate > @StartDate

Problem

I have a table(e.g. tableA) like this: ``` | Name | Startdate | Enddate | |---------------------------------| | a | 2012-07-01 | 2013-06-30 | | b | 2011-05-01 | 2012-04-30 | | c | 2010-01-01 | 2013-12-31 | | d | 2013-01-01 | 2014-12-31 | | e | 2011-07-01 | 2012-06-30 | ``` I want to get a name from the table who is active between `2012-05-01` and `2012-10-31`. From above table the result should be a,c and e. What I have done is as following: ``` SELECT Name FROM tableA WHERE startdate<='2012-05-01' AND enddate>='2012-10-31' ``` But I am not getting the correct result.

Original source