Using join to get date diff (Oracle)

Posted on 2010-01-05
Last Modified: 2013-12-07

I want to use join or any method to get date diff by comparing the date in result of the following query with sysdate
 SELECT   COUNT (1) total, admin,DECODE (location, 'head', 'head', 'OUT') location,mydate
        FROM stocks
         WHERE status= 'PENDING' AND pending_with = 'user'  and location<>'head'
          GROUP BY mydate,m_admin,DECODE (location, 'head', 'head', 'OUT') ORDER BY admin, DECODE (location, 'head', 'head', 'OUT')

The result of this query is somthing like

TOTAL admin            location       mydate

1      user1       OUT      12/21/2009 5:58:41 PM
1      user1             OUT      12/26/2009 11:36:16 AM
1      user1             OUT      12/21/2009 6:00:40 PM
1      user1             OUT      12/21/2009 5:28:47 PM
1      user1             OUT      12/26/2009 11:33:04 AM
1      user1             OUT      12/29/2009 8:23:17 AM
1      user1             OUT      12/26/2009 10:54:29 AM

Now I want this query to be modified to get another value as duration which is a comparison between sysdate and mydate
datediff(dd,mydate,getdate()) as duration
but this gives error when  I put it in the query

Any idea on how to get it work.



Question by:zaki100
    LVL 142

    Accepted Solution

    oracle does not have the "datediff" function...
    you could just use this expression:

    sysdate - mydate
    LVL 15

    Expert Comment

    by:Shaju Kumbalath
    or Trunc(sysdate)-trunc(mydate)
    LVL 31

    Expert Comment

    Is this MS SQL Server or Oracle?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now