Sql query to find the dates between two

Question

I have a problem that I would like to get the dates which are logged between two dates (i.e start& end dates).

I have a table with the following records in it:

Create table t ( a int, b date, c date );

insert into t values(1, to_date( '01-jan-2015'), to_date( '15-jan-2015'));
insert into t values(2, to_date( '03-jan-2015'), to_date( '10-jan-2015'));
insert into t values(3, to_date( '12-jan-2015'), to_date( '25-jan-2015'));
insert into t values(4, to_date( '20-jan-2015'), to_date( '01-feb-2015'));
insert into t values(5, to_date( '05-feb-2015'), to_date( '10-feb-2015'));

Is there any way using a SELECT statement to determine the dates that exist within the date range given in this table?

EX: the value between '01-jan-2015' to_date '15-jan-2015' is already there. Anyways there is again two values logged on '03-jan-2015' and '12-jan-2015'. (whereas those were alreday defined between('01-jan-2015' to_date '15-jan-2015')) So, I need to get the output as "values for start_date "'03-jan-2015' is already logged" without giving the date range in the query. that is, it should automatically pull the record for given value.

This assumes that when date ranges overlap, it will eliminate a overload of values between the date range

Hope this makes sense.Kindly help me on to solve this.

Thanks, Shruthi

This is almost the same scenario that you can adjust a little to apply to your problem ... https://stackoverflow.com/questions/48755634/select-between-two-date/48756731#48756731 — GaryL, Feb 15 '18 at 06:16

score 2 · Answer 1 · answered Feb 15 '18 at 05:51

2

Try this

select * FROM AnyTable WHERE 
              start_date  <= '03-jan-2015' and end_date  >= '03-jan-2015'

answered Feb 15 '18 at 05:51

Yahya Mukhtar

474
5
13

Abdul Rasheed · Accepted Answer · 2018-02-15T06:47:09.920

2

Try the below query, that will list out all the overlapping entries.

SELECT  *
FROM    t as t1
WHERE EXISTS (
        SELECT  1
        FROM    t as t2
        WHERE   t1.a != t2.a
            AND t1.b <= t2.c AND t1.c >= t2.b
    )

Or try this with a left join

SELECT  t1.*,t2.a as overlap_id
FROM    t as t1
LEFT JOIN t as t2   ON t1.a > t2.a
            AND t1.b <= t2.c AND t1.c >= t2.b
WHERE   t2.a IS NOT NULL

a   b           c           overlap_id
--------------------------------------
2   2015-01-03  2015-01-10  1         --2 overlapped with 1
3   2015-01-12  2015-01-25  1         --3 overlapped with 1
4   2015-01-20  2015-02-01  3         --4 overlapped with 3

edited Feb 15 '18 at 06:47

answered Feb 15 '18 at 06:06

Abdul Rasheed

6,486
4
32
48

Thanks Abdul. But ,How do i get the records that are made in between (If logged) the overlapping entries. i.e I need to derive only the entries for "'03-jan-2015'" (from above data given) without giving the date range – Shruthi Feb 15 '18 at 06:21
in the data u give, record 1 is overlapped with record 2 and vice versa, also the record 3 is overlapped with 4 and vice versa. – Abdul Rasheed Feb 15 '18 at 06:23
Also the record 3 is overlapped with both 1 and 4. – Abdul Rasheed Feb 15 '18 at 06:58

Sql query to find the dates between two

2 Answers2