Get the range of dates over multirows which are not in a given range

Question

Suppose that I hava a table T, with

Create Table T (id int, date Datetime);
Insert Into T (id,date) Values 
(1,'2012-12-11'),(2,'2012-12-12'),
(3,'2012-12-13'),(4,'2012-12-15'),
(5,'2012-12-17'),(6,'2012-12-18'),(7,'2012-12-19');

And suppose the given range of date are form 2012-12-09 to 2012-12-16, I want to get the range of date in T, such that is not in the range. I try this code:

Select date From T where '2012-12-09'<= date AND date <= '2012-12-16';

It output all the data in the range, but this is NOT I hope to get, what I need is that to return something like:

start       end
2012-12-09  2012-12-10
2012-12-14  2012-12-14
2012-12-16  2012-12-16

~~That is the range not in the given one.~~ (See the comment below), That is the range not in T (but in the given one, i.e, '2012-12-09'-'2012-12-16').

As a test, you can try it here: http://sqlfiddle.com/#!2/e55ba/1

@SinsilMathew they are the range of date NOT in T but in the range '2012-12-09' and '2012-12-16'; — van abel, Jan 22 '15 at 15:56
Try this select date from T where date BETWEEN '2012-12-09' AND '2012-12-16' — Sinsil Mathew, Jan 22 '15 at 16:14
@SinsilMathew I don't see there are any difference of my original code, and this as I mentioned, is not I wanted! — van abel, Jan 22 '15 at 16:18
Select date From T where #2012-12-09#<= date AND date <= #2012-12-16#; — Sinsil Mathew, Jan 22 '15 at 16:25
and I find http://stackoverflow.com/a/12276797/1910004 is helpful. — van abel, Jan 23 '15 at 06:46
@vanabel You can't select what isn't there. Therefore you have to create a table with "helper" dates. See the first part of this answer: http://stackoverflow.com/a/13563322/447489 Then you can `left join` this table and the rest should be easy to figure out. — fancyPants, Jan 23 '15 at 07:52

score 0 · Answer 1 · answered Jan 22 '15 at 18:52

0

Just try this where condition in your query then you will get all the result that are not in range. where '2012-12-09'<= date AND date >= '2012-12-16'

answered Jan 22 '15 at 18:52

Adal Singh

106
2

Thanks, but you were misunderstanding me. – van abel Jan 23 '15 at 04:00

Get the range of dates over multirows which are not in a given range

1 Answers1