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, Jul 12 '12 at 06:13
Do you want the results that are valid for the entire period? — Robert, Jul 12 '12 at 06:14
@RobB Yes. I want the results that are valid for the given period(between 2012-05-01 and 2012-10-31) — Himanshu, Jul 12 '12 at 06:16
Have not used ms-sql for a while, but in oracle you will need to convert string '2012-05-01' to date before you could compare the dates, otherwise you will end up comparing strings like 'a'<'b'. — Kamal, Jul 12 '12 at 06:18
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, Jul 12 '12 at 06:23
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, Jul 12 '12 at 06:24
@marc_s and @Damien_The_Unbeliever Right I will use `yyyyMMDD` — Himanshu, Jul 12 '12 at 06:31

Mikael Eriksson · Accepted Answer · 2012-07-12T07:14:44.300

5

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

edited Jul 12 '12 at 07:14

answered Jul 12 '12 at 06:19

Mikael Eriksson

136,425
22
210
281

1

I got the result what I wanted. [See this fiddle](http://sqlfiddle.com/#!3/2d403/1) – Himanshu Jul 12 '12 at 06:35
1

New fiddle with date format `yyyyMMdd` is updated [here](http://sqlfiddle.com/#!3/7619d/1) – Himanshu Jul 12 '12 at 07:28
I want to vote this ++++. Seemingly simple, but when you've been staring at this sort of problem for a while, you don't quite see it. Thanks. – Mike Feb 11 '13 at 23:45

Robert · Answer 2 · 2012-07-12T06:17:34.690

0

If you are wanting to find any results that occurred during the period then utilize BETWEEN:

SELECT Name FROM tableA WHERE startdate BETWEEN '2012-05-01' and '2012-10-31'

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.

edited Jul 12 '12 at 06:17

answered Jul 12 '12 at 06:12

Robert

8,717
2
27
34

If I use `startdate BETWEEN '2012-05-01' and '2012-10-31'` then I will not get **a** as it is active in that period. – Himanshu Jul 12 '12 at 06:19
According to your question `a` has an enddate of '2013-06-30'. The BETWEEN expression will only return a match if both the startdate and enddate are within (between) the specified dates. – Robert Jul 12 '12 at 06:24

score 0 · Answer 3 · edited Jul 12 '12 at 06:16

0

Placement of comparison operator were creating problem check this

SELECT Name FROM tableA WHERE startdate>='2012-05-01' AND enddate<='2012-10-31'

edited Jul 12 '12 at 06:16

Vishwanath Dalvi

35,388
41
123
155

answered Jul 12 '12 at 06:14

Deepesh

5,346
6
30
45

You should try this query against the data. It does not return any rows at all. – Mikael Eriksson Jul 12 '12 at 07:11
@MikaelEriksson It wont because table does not contain any record in the given date range. As he has asked in question record active between " active between '2012-05-01' and '2012-10-31'" means start date of the record should be greater then or equal to 2012-05-01 but if question is to have all the records in which this date range comes then your query is right. May be i have interpreted it wrong!!! – Deepesh Jul 12 '12 at 07:19
SELECT Name FROM table1 WHERE startdate between '2012-05-01' AND '2012-10-31' or enddate between '2012-05-01' AND '2012-10-31' – Deepesh Jul 12 '12 at 07:24
1

OP wants overlapping date ranges. Not entirely within the interval. – Mikael Eriksson Jul 12 '12 at 07:24
@Deepesh See the question again. I wanted the result where `'2012-05-01'` and `'2012-10-31'` exist between _startdate_ and _enddate_ – Himanshu Jul 12 '12 at 07:25

SQL query confusion for startdate and enddate

3 Answers3