A Date between dates?

[Zcumbag]

Hi!

I'm trying to create a query that selects all rows where any given date lays beween my VALID_FROM and VALID_TO datevalues more than on one ocction.

so if my table looks like this:

ID VALID_FROM VALID_TO
10 1999-01-01 1999-12-31
11 1999-06-01 1999-12-31
12 2000-01-01 2000-12-31

I would like my query to return rows 10 and 11 because the dates between 1999-06-01 and 1999-12-31 is valid in those two rows.

possible? thanks in advance.

[sridhar_423]

select ID from TABLE_NAME where to_date('1-1-2000','dd-mm-yyyy') between valid_from and valid_to

will return the last row. . like wise

[Zcumbag]

thanks sridhar... however... what i'm trying to achieve is to select the rows without myself having to but a date in there... is this posible through a query or do I have to loop through all dates in a procedure?

[sridhar_423]

what? you dont have a date? I didnot understand.. without having atleast one parameter, how are you supposed to put a condition? Do you mean to join two tables?if it is so.. then the query will look like this...

select ID from TABLE_NAME a,REF_TABLE b where b.REF_DATE between a.valid_from and a.valid_to

i'm not sure whether i have answered in your context or not....

[Zcumbag]

I do not intend to join tables.

I only have this one table. Generally all valid_from and valid_to dates range over a full year. But sometimes two date-ranges overlap. I want to select all the rows where two date-ranges overlap. Such as post 10 and 11 above.

clearer?

I'm guessing this involves either a subquery or a join to the same table, but i can't put it together.

[sridhar_423]

select a.id id1,b.id id2 from test_table a,test_table b
where
a.id<>b.id and a.valid_from>=b.valid_from and a.VALID_TO<=b.VALID_TO

but this query gives the o/p in two columns.. and also as the number of records increases, the same set gets repeated. like 1,2 and 2,1.
cross-check query.. so tat it doesnot fail in any of the cases.