Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DATEDIFF function work in Oracle database or not ?

945595Oct 29 2012 — edited Oct 29 2012
Hai all,

I like to know whether DATEDIFF function work in Oracle database or not.
This post has been answered by Stew Ashton on Oct 29 2012
Jump to Answer

Comments

915396
Uvaraja wrote:
Hai all,

I like to know whether DATEDIFF function work in Oracle database or not.
I guess, you are coming from some other database.
That's Ok.

No, there's no such function here. Instead, you can do date1-date2 and get the difference.
Try this... 
/* Formatted on 10/29/2012 4:03:17 PM (QP5 v5.163.1008.3004) */
SELECT TRUNC (SYSDATE) - TO_DATE ('10/20/2012', 'mm/dd/yyyy') 
    FROM DUAL;
HTH
Ranit B.

Edited by: ranit B on Oct 29, 2012 4:13 PM
-- sample code added
Stew Ashton
Answer
To add to what Ranit said, ENDDATE - STARTDATE will give you a number that corresponds to the number of days between the two dates.

If you want the result in hours, multiply by 24; if minutes, multiply by 24*60 and so forth.

You can also convert the result to an INTERVAL. There are two type of intervals:
NUMTODSINTERVAL(ENDDATE - STARTDATE, 'DAY') or NUMTOYMINTERVAL(ENDDATE - STARTDATE, 'DAY')
Marked as Answer by 945595 · Sep 27 2020
1 person found this helpful
915396
Stew Ashton wrote:
To add to what Ranit said, ENDDATE - STARTDATE will give you a number that corresponds to the number of days between the two dates.

If you want the result in hours, multiply by 24; if minutes, multiply by 24*60 and so forth.

You can also convert the result to an INTERVAL. There are two type of intervals:
NUMTODSINTERVAL(ENDDATE - STARTDATE, 'DAY') or NUMTOYMINTERVAL(ENDDATE - STARTDATE, 'DAY')
Awesome Stew!

Yesterday only i got this doubt regarding NUMTODSINTERVAL... i read through docs and few articles, but nothing is getting into my grey matter. :-)

Can you please explain this in brief?
This will also be useful for others.

Ranit B.
BluShadow
And to add to what Stew and Ranit have said... if you need the months between two dates, there's a special function for that...
SQL> select months_between(sysdate, date '2011-04-18') from dual;

MONTHS_BETWEEN(SYSDATE,DATE'2011-04-18')
----------------------------------------
                                18.37051
So, number of years between two dates can also be achieved with the same function and dividing by 12...
SQL> select months_between(sysdate, date '2011-04-18')/12 from dual;

MONTHS_BETWEEN(SYSDATE,DATE'2011-04-18')/12
-------------------------------------------
                                 1.53087867
945595
At All, Thanks for replying me. I got it. I had a slight confuse whether it work or not that's why I posted it.

Regards,
Uraja
Stew Ashton
An INTERVAL gives you a period of time. There are two types: INTERVAL YEAR TO MONTH, to store a difference in years and months, and INTERVAL DAY TO SECOND, to store a difference in days, hours, minutes and seconds (including fractional seconds).

If you subtract a TIMESTAMP from another TIMESTAMP, you get an INTERVAL.

NUMTODSINTERVAL turns a number into an INTERVAL DAY TO SECOND. You have to say what the number "means": it can mean days, hours, minutes or seconds.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 26 2012
Added on Oct 29 2012
6 comments
220,775 views