Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    THAILAND
    Posts
    15

    Unhappy Unanswered: how to calculate a date with sysdate and return with minute

    Could you please help me?

    I'd like to calculate difference date between a date and sysdate and return with minute. ( Oracle Database ) ,please help me.
    1

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not sure what you mean by "return with minute". Say the given date is 23-AUG-2004 00:00:00 and SYSDATE = 24-AUG-2004 13:04:30. What do you want to see:

    1) 1.54479167 days
    2) 2224.5 minutes
    3) 1 day, 13 hours and 4.5 minutes

    ... or something else?

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    I Think he want to see the third one, but is this possible to get by date funktions ? .. I have a lot of experience in DB2 timestamp handling and calculating. DB2 is there a leader in time functionality, but exactly that is not possible.

    how much minutes are bygone between time 0 and sysdate ? leap years are quiet importand for that, and specially the leap day in the years...

  4. #4
    Join Date
    Aug 2004
    Posts
    9
    shit .. all 3 items are the same

  5. #5
    Join Date
    Mar 2004
    Location
    THAILAND
    Posts
    15

    thanks you

    please show all result please.
    1

  6. #6
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    by day

    When performing date arithmetic, Oracle returns the amount in days. It would be a matter of multiplying up to minutes:

    select ((sysdate - <date value>) * 24) * 60 from dual;

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jupiter_111
    please show all result please.
    Again, not sure what you mean. If you mean, show how to get all the 3 results I showed earlier then the first 2 are easy:

    1) select sysdate-datecol from mytable;

    2) select (sysdate-datecol)*24*60 from mytable;

    The 3rd is more difficult. You could defien a function like this:
    Code:
    SQL> CREATE OR REPLACE function days_to_time( p_days in integer ) return varchar2
      2  is
      3    d integer;
      4    h integer;
      5    m integer;
      6  begin
      7    d := floor(p_days);
      8    h := floor((p_days-d)*24);
      9    m := floor(((p_days-d)*24-h)*60);
     10    return d||' days '||h||' hours and '||m||' minutes';
     11* end;
    SQL> /
    
    Function created.
    
    SQL> select days_to_time( (to_date('24-aug-2004 13:04:30','DD-MON-YYYY HH24:MI:SS')
      2           - to_date('23-aug-2004','DD-MON-YYYY') ) )
      3  from dual;
    
    DAYS_TO_TIME((TO_DATE('24-AUG-200413:04:30','DD-MON-YYYYHH24:MI:SS')-TO_DATE('23
    --------------------------------------------------------------------------------
    1 days 13 hours and 4 minutes

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Hi,


    The same result could be achieved by a single query:

    HTML Code:
    select decode( sign(s.num-1), 1, trunc(num) ||' days, ', 'x') ||
    	 round(mod(s.num,1)*24) ||' hours and ' ||
    	 round(mod( mod(s.num,1)*24,1)*60,2) || ' minutes ' as interval
    from (
    SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
    ) s
    /
     
    For example, runing this query yields:

    HTML Code:
     SQL> r
    select decode( sign(s.num-1), 1, trunc(num) ||' days, ', 'x') ||
    round(mod(s.num,1)*24) ||' hours and ' ||
    round(mod( mod(s.num,1)*24,1)*60,2) || ' minutes ' as interval
    from (
    SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
    ) s
    Enter value for dt: 2004-08-23
    old 5: SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
    new 5: SELECT sysdate - to_date('2004-08-23', 'yyyy-mm-dd') num from dual
    INTERVAL
    --------------------------------------------------------------------------------
    1 days, 13 hours and 57.4 minutes
    Thanks,
    Ravi

  9. #9
    Join Date
    Mar 2004
    Location
    THAILAND
    Posts
    15

    Many Thanks for all of you.

    It's perfect.

    Thank a lot
    1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •