6

Confusion of SQL queries to start and finish

 2 years ago
source link: https://www.codesd.com/item/confusion-of-sql-queries-to-start-and-finish.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Confusion of SQL queries to start and finish

advertisements

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.

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK