Re: Difference Between Dates

A copy of this was sent to nedmunds_at_my-dejanews.com (if that email address didn't require changing) On Tue, 15 Sep 1998 16:38:05 GMT, you wrote:

>I am wondering how I figure out the difference between 2 dates.
>For example: (I really need the amount or hours or minutes)
>
>05-AUG-1998 10:50:00 and 06-AUG-1998 23:56:00
>
>Simply subtracting them produces some unusual results.
>
>Thanks in advance
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

you can get either TOTAL (days, hours, minutes, seconds) between 2 dates simply by subtracting them or with a little mod'ing you can get Days/Hours/Minutes/Seconds between.

To get the hours between 2 times, simply:

select ( date1 - date2 ) * 24 from T;

thats the number of hours (including the fractional component of an hour so you might get something like 3.1232253 meaning 3 hours and 1/12'th of an hour.

To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following:

select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),

       trunc( sysdate-created ) "Dy",
       trunc( mod( (sysdate-created)*24, 24 ) )  "Hr",
       trunc( mod( (sysdate-created)*24*60, 60 ) )  "Mi",
 trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec",
       to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
       sysdate-created "Tdy",
       (sysdate-created)*24 "Thr",
       (sysdate-created)*26*60 "Tmi",
       (sysdate-created)*26*60*60 "Tsec"

Dy gives you number of days between 2 dates (partial days discarded). Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days)

Hr/Thr = hours
Mi/Tmi = minutes
Sec/Tsec = seconds...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.