Oracle SQL query question

Question

I want to extract data from database where field "end_ date" is less than today's date and where end_date is not equal to null; how would I do that? I figured the second part out .. I have trouble with first part

select * 
 from table 
where to_char(end_date) IS NOT null

Answer 1

Try this

   ... 
   Where end_ date is not null and 
      And end_date < sysdate

And, actually, I think you may not even need the first part, as if end_date is null, it certainly will not be after todays date, so try this by itself:

Where end_date < sysdate

EDIT NOTEs:

1. This will give you every record that has an end_date prior to the current system date and time, including those with end_Date values earlier today. If you want only those with end_Dates up to and not including midnight last night, then you need to replace "sysDate" with an expression for the date time value for midnight last night..

   Where end_date < trunc(sysdate)

2. sysdate is oracle specific syntax to return the current system date and time. (in SQL Server, for e.g., it's a function called getDate() . The ANSI-Standard Syntax for this functionality, (which works in both Oracle and SQL Server, and should work in any ANSI-standard database) is *CURRENT_TIMESTAMP*.

Answer 2

To show you a little more on how to use the Dates in Oracle check this out:

Selects the current date and removes 30 minutes from it where check_date is declared as a timestamp.

select current_timestamp + interval '-30' minute into check_date from dual;

Selects everything from table where end_date is not null and above now - 30 minutes

select * from table
where to_char(end_date) is not null and end_date < check_date;

Answer 3

select * from table where end_date < CURRENT_DATE and end_date IS NOT null