Results 1 to 2 of 2

Thread: Oracle SQL: Difference between two dates in minutes

  1. #1

    Thread Starter
    Addicted Member Guru's Avatar
    Join Date
    May 2000
    Location
    sulking in the cupboard under the stairs
    Posts
    237

    Oracle SQL: Difference between two dates in minutes

    I have a date field containing date and time.

    How do I calculate the difference in minutes between this date and sysdate?






    Thanks
    Another light-hearted post from Guru

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Oracle SQL: Difference between two dates in minutes

    Code:
    select inst.thedate,
      instr(inst.thedate,':') FirstOne,
      instr(inst.thedate,':',instr(inst.thedate,':')+1) SecondOne,
      substr(Inst.thedate,instr(inst.thedate,':')-2,2) TheHours,
      substr(Inst.thedate,instr(inst.thedate,':')+1,2) TheMins,
      substr(Inst.thedate,instr(inst.thedate,':',instr(inst.thedate,':')+1)+1,2) TheSecs,
      to_number(substr(Inst.thedate,instr(inst.thedate,':')-2,2))*60 + to_number(substr(Inst.thedate,instr(inst.thedate,':')+1,2)) TheTotalMins
    From 
    (select to_char(localtimestamp-to_timestamp('28-jul-05 12.20.53.466428')) thedate 
    from tablename 
    where tablename.id=1048) Inst
    The long way.
    I expect there is a function somewhere in Oracle that does it, but I couldn't see it straight off.

    Edit:
    Also found an Extract function - although I couldn't get it to work.
    Last edited by Ecniv; Jul 28th, 2005 at 07:02 AM. Reason: Extra info

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Click Here to Expand Forum to Full Width