Re: Date Difference

In article <3f4arp$f0e_at_crchha60.bnr.ca> bartok_at_bnr.ca (Vince Cross) writes:

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

Hope this helps - MR
>
>Enjoy,
>Vince
>--
>* disclaimer - My views respresent NT/BNR in every way, NOT!
>* Please direct non-business email to VLCross_at_aol.com (No, I'm not an idiot
> just because I have an aol account. Excessive drinking makes me one.)
>* Work related stuff can go to bartok_at_bnr.ca

                                 _                          _  
     |\ | _ ._ _|_ |_ ._ _ ._   /__ ._   ._ _ ._ _  _.._   /  _ ._ _
     | \|(_)|   |_ | || (_)|_)  \_| | |_|| | || | |(_|| |  \_(_)| |_). 
/--------------------------|---.----------------------------------|----------\
| Michael D. Resnick           |     mresnick_at_world.nad.northrop.com         |
| Northrop Grumman Corporation |     Voice: 310.332.7807                     |
| Military Aircraft - ILS LIS  |     Fax:   310.332.7816                     |
| One Northrop Ave.  2555/W6   |---------------------------------------------|
| Hawthorne, CA  90250         | "Someday I'll quote myself and put it here" |
 \------------------------------^---------------------------------------------/