Re: Date arithmetic
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