SQL query confusion for startdate and enddate

Question

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.

the right should be: startdate>='2012-05-01' AND enddate<='2012-10-31' — rad
Do you want the results that are valid for the entire period? — Robert
You should use the proper, language-independent format for specifying just dates against DATETIME - and that format is yyyyMMdd - so try: ...WHERE startdate <= '20120501' AND enddate >= '20121031'. The format with the dashes is not safe and can be misinterpreted depending on the language setting of your SQL Server. Plus your comparison operators should be reversed, too - it should be greater or equal to 20120501 and smaller or equal to 20121031 - right? — marc_s
Since no-one's mentioned it in any of their answers - it's best to avoid specifying dates (without times) as YYYY-MM-DD - they can be ambiguous to SQL Server. Better to just use YYYYMMDD with no separator characters. — Damien_The_Unbeliever
@marc_s and @Damien_The_Unbeliever Right I will use yyyyMMDD — Himanshu Jansari

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2012-07-12T06:19:50

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

SQL query confusion for startdate and enddate

3 Answers