Re: Date arithmetic

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 25 Jan 1999 12:11:14 GMT
Message-ID: <36b35f49.23388430_at_192.86.155.100>


A copy of this was sent to Krankenhaus-Hameln_at_t-online.de (Lars Reineke) (if that email address didn't require changing) On Fri, 22 Jan 1999 13:22:07 GMT, you wrote:

>Hi!
>
>How do I get the difference of two dates in days in Dev2K?
>
>I want to get the age of an entry, its creation date is stored in a date
>field, and is displayed DD.MM.YYYY HH24:MI.
>Now I want to calculate the days between sysdate and the value in the
>field.
>
>Can you help me out?
>

[Quoted] 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"
from all_users
where rownum < 50
/

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...

>Thanks in advance.
>
>---
>Lars Reineke
>http://www.planet-interkom.de/pinhead
>mailto:reineke_at_kreiskrankenhaus-hameln.de
>---
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
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.
Received on Mon Jan 25 1999 - 13:11:14 CET

Original text of this message