Date Range in PL/SQL

Question

If I have table with a Date column (Date field) called created_date, with values like "9/2/2010 5:25:42 PM".

I want to select all rows from a start_date to a end_date. However, the end_date may be null. In this case, I want to select all rows where created_date is greater than end_date.

Answer 1

Why just use a simple SQL query for that, like this one:

select xxx from table_names where created_date is null or (created_date >= to_date("02/09/2010", "dd/mm/yyyy") and created_date <= to_date("03/09/2010", "dd/mm/yyyy"));

Edit

You can define a query like the one defined by ammoQ, i.e. something like that:

select xxx from table_names where created_date is null or created_date >= start_date and created_date <= nvl(end_date, to_date("31/12/9999", "dd/mm/yyyy"));

However, as you are using PL/SQL, you can check the nullability of end_date parameter:

IF end_date IS NULL THEN
    select xxx from table_names where created_date is null or created_date >= start_date;
ELSIF
    select xxx from table_names where created_date is null or created_date >= start_date and created_date <= end_date;
END IF;

Note that you can remove the created_date is null condition if created_date is not a nullable column...

Answer 2

If i took it right from your question this should work:

SELECT *
FROM yourTable
WHERE created_date >= to_date('01.09.2010', 'dd.mm.yyyy')
  AND (end_date  <= to_date('02.09.2010', 'dd.mm.yyyy')
  OR end_date   IS NULL);

Answer 3

select * from TABLE

where created_date >= '2010-09-02'

and (created_date is NULL or created_date <= '2010-09-03')

Answer 4

Since toDate (which can be null) is a host variable, it's easier than the solutions already given (which are all wrong in that regard, btw)

select * from mytable
  where created_date between v_fromdate
                         and nvl(v_todate, to_date('31.12.9999','dd.mm.yyyy'));

Answer 5

select * from yourtable
where created_date >= @StartDate AND created_date <=ISNULL(@EndDate,created_date)

Answer 6

SELECT *
  FROM A_TABLE
  WHERE CREATED_DATE >= &START_DATE AND
        (CREATED_DATE <= &END_DATE OR
         &END_DATE IS NULL)