Re: ??? Number of days betwwen two dates?

Ed Bradt wrote:
>
> all you need to do is subtract the 2 dates, the result is in days:
> SQL> select to_date('3/2/97','mm/dd/yy')-to_date('2/1/97','mm/dd/yy')
> from dual;
>
> TO_DATE('3/2/97','MM/DD/YY')-TO_DATE('2/1/97','MM/DD/YY')
> ---------------------------------------------------------
> 29
> Ed Bradt
>

Don't forget that dates are stored as integers, and 1 = 1 day. So, if you subtract 2 date fields that also contain times, you will get a real number result, rather than an integer. You will need to use round or floor to convert the number to an integer.

For example, suppose you wanted to know how old your objects are:

  select object_name, object_type, floor(sysdate - created)   from user_objects;

OBJECT_NAME                    OBJECT_TYPE   FLOOR(SYSDATE-CREATED)
------------------------------ ------------- ----------------------
BOB                            TABLE                            141
BOB1                           PROCEDURE                         42
BOB2                           PROCEDURE                         42
BOBTEMP                        TABLE                              1
BOB_30_XXXXXXXXXXXXXXXXXXXXXXX TABLE                             93
BOB_TRIGGER                    TRIGGER                           42
COUNT_BLOCKS_TEMP              TABLE                            122
PLAN_TABLE                     TABLE                            133
READPROC                       PROCEDURE                          1
READPROC2                      PROCEDURE                          1
TEST                           TABLE                             17

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com