Date Range Query in Oracle

1
votes

I have following data

Sr. FromDate ToDate Code

1   1990-01-01  2000-08-31  A        
2   2000-09-01  2001-05-31  B        
3   2001-06-01  2018-12-31  C

and need to write SQL query to find rows having code for date range= fromdate 1992-01-01 and ToDate 2000-12-31.

Select * 
From Table 
Where fromDate <= 1992-01-01 
and EndDate >=2000-12-31

not returning proper data.

Any help??

Expected output are first two rows which cover part of date mentioned in query.

One of possible query is:

Select * From table where fromdate <= 19920101 UNION Select * From table where todate >= 20011231

But some how I don't like it and wanted easier alternative.

4
sqloracle
Have you tried "between '1992-01-01' and '2000-12-31'"? - durbnpoisn
It is returning proper data - you don't have any rows with fromdate less than 1992-01-01 and end date greater than 2000-12-31. Are you sure you didn't mean to say fromdate GREATER than 1992-01-01 and end date LESS than 2000-12-31 ? - StevieG
Expected output first two rows. First row has from date less than 1992-01-01 and second row has end date greater than 2000-12-31. - user3507651

4 Answers

0
votes

Switch the operators

select * 
from Table 
where fromDate >= '1992-01-01'
and EndDate <= '2000-12-31'
0
votes

You should migrate the dat to a date object:

Select *
from table
where fromDate >= to_date('1992-01-01','YYYY-MM-DD') 
and ...

Look here for valid date formats and syntax: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

0
votes

Try reverse filtering, I mean change the dates in filtering:

WITH tab(ID, start_date, end_date, code) AS (
    SELECT 1, to_date('1990-01-01', 'YYYY-MM-DD'), to_date('2000-08-31', 'YYYY-MM-DD'), 'A' FROM dual UNION ALL        
    SELECT 2, to_date('2000-09-01', 'YYYY-MM-DD'), to_date('2001-05-31', 'YYYY-MM-DD'), 'B' FROM dual UNION ALL
    SELECT 3, to_date('2001-06-01', 'YYYY-MM-DD'), to_date('2018-12-31', 'YYYY-MM-DD'), 'C' FROM dual)
-----------------
--End of data preparation
-----------------
SELECT * 
  FROM tab
 WHERE end_date >= to_date('1992-01-01', 'YYYY-MM-DD') 
   AND start_date <= to_date('2000-12-31', 'YYYY-MM-DD');

Output

| ID |                       START_DATE |                      END_DATE | CODE |
|----|----------------------------------|-------------------------------|------|
|  1 |   January, 01 1990 00:00:00+0000 | August, 31 2000 00:00:00+0000 |    A |
|  2 | September, 01 2000 00:00:00+0000 |    May, 31 2001 00:00:00+0000 |    B |
0
votes

Based on what you said you have to put an OR. Is it really what you need?

Select *
From Table 
Where fromDate <= '1992-01-01'
OR EndDate >='2000-12-31'