How to calculate the date differences in days using pl-sql 2

[cyan01]

Is there a way to write a sql statement so that it returns the date difference in days?

More specifically, say I have a table containing a field whose datatype is 'date'. How can I retrieve the date difference in days between a given row in this field and the sysdate?

For instance, if a value in this column is 01-01-2005 and today (sysdate) is 01-04-2005, I want the result is returned as 3 ( the datetime difference in days).

Thanks for you help.

 

[lewisp]

Just subtract one date from the other. In Oracle, that returns a number of days by default.

 

[carp]

Be advised that subtracting dates will return the difference in days, including any fractional portion thereof - consequently, your result might be 3.27 instead of 3. If this is a problem for you, the following might take care of it:

DECLARE
   l_days_apart NUMBER;
BEGIN
   FOR i IN (SELECT date_column FROM my_table) LOOP
      l_days_apart := TRUNC(sysdate - i.date_column);
      .
      .
      .
   END LOOP;
END;