Re: Date Difference

Mike D. Resnick (mresnick_at_world.nad.northrop.com) wrote:
: In article <3f4arp$f0e_at_crchha60.bnr.ca> bartok_at_bnr.ca (Vince Cross) writes:
: >In article <3f42cq$3un_at_portal.gmu.edu>,
: >Ajay M Gandhi <agandhi_at_mason1.gmu.edu> wrote:
: >>Hi,
: >>
: >>Is there any function that can find the number of hours and
: >>minutes between two date functions?
: >>
: >>For example, I am interested in getting 02:30 with the follwoing
: >>data.
: >>
: >>Start date = 12-Jan-1995
: >>End Date = 12-Jan-1995.
: >>
: >>Both the above dates were updated into the database at an
: >>interval of 2 hrs and 30 mins.
: >>
: >
: >This might work for you:
: >
: >select TO_NUMBER(TO_CHAR(end_date, 'HH24'))-TO_NUMBER(TO_CHAR(start_date,
: > 'HH24')) || ':' || TO_NUMBER(TO_CHAR(end_date, 'MI'))-TO_NUMBER(
: > TO_CHAR(start_date, 'MI'))
: >from table_name;
 

: This will only work if the time was given when the record was inserted or
: updated. (If your NLS_DATE_FORMAT does not include the time portion of the
: date and you don't explictly give it, your date won't include the time.)
: For example: (NLS_DATE_FORMAT='dd-MON-RR')
 

: SQL> create table table1 (start_dt date, end_dt date);
: Table created.
: SQL> insert into table1
: 2 values('31-dec-95',to_date('31-dec-95 1322','dd-mon-yy HH24MI'));
: 1 row created.
: SQL> select to_char(start_dt,'dd-mon-yy HH24MI'),
: 2 to_char(end_dt,'dd-mon-yy HH24MI') from table1;
: TO_CHAR(START_DT,'DD-MON-YYHH24MI') TO_CHAR(END_DT,'DD-MON-YYHH24MI')
: ----------------------------------- ----------------------------------
: 31-dec-95 0000 31-dec-95 1322
 

: Notice that the time portion of the first date field is not there. Once you are
: sure that the time portion *is* there, then use Vince's select to give the
: data in the correct format. (I had to enclose the selected fields with parens
: to make Vince's select work) You may also omit the TO-NUMBER functions:
: SQL> select (TO_CHAR(field2,'HH24')-TO_CHAR(field1,'HH24'))||':'||
: 2 (TO_CHAR(field2,'MI')-TO_CHAR(field1,'MI')) "DIFF" from table1;
: DIFF
: ------
: 13:22

Another angle:

	For Example : End_date = '12-JAN-1995-02:30:00'
and                  Start_date = '11-JAN-1995-03:30:00'

Now we apply the above select clause, then we get the hour difference as '-1'. which is not what I had asked for.

Anyway thanks for replying back.

Ajay
SIRC
: Hope this helps - MR
: >
: >Enjoy,
: >Vince

--
*******************************************************************
SIRC => Software Information Resource Corp. - Meeting Future Needs
*******************************************************************