question

OracleApprender avatar image
OracleApprender asked

Which function is used to find the difference between two dates ?

In my PL/SQL procedure I need to implement the logic of finding the difference between two dates. If anyone knows the answer then please share with me and also let me know the syntax of that function as well.

queryoracleplsql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image
HillbillyToad answered

You can just subtract them. Here's an example.

declare
 date1 date;
 date2 date;
 date3 number(7,2);
begin
 select sysdate into date1 from dual;
 select (sysdate-30) into date2 from dual;
 date3:= date1 - date2;
 dbms_output.put_line('Date1:' || date1 || '  Date2:' || date2 || '  Date3:' || date3);
end;
/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for your answer. Do you know any function existed for the same instead of the entire procedure?
0 Likes 0 ·
No procedure necessary, just subtract the 2 values.
0 Likes 0 ·
coskan avatar image
coskan answered

If you are looking for datediff like in MSSQL it does not exist.

You can use the function below (probably from tom kyte) you can also edit it for your own use

create or replace function datediff( p_what in varchar2, p_d1 in date, p_d2 in date ) return number as l_result number; begin select (p_d2-p_d1) * decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) into l_result from dual;

 return l_result;

end; /

call it like

SQL> select datediff('hh',sysdate-50,sysdate) from dual 2 ;

DATEDIFF('HH',SYSDATE-50,SYSDATE)

                         1200
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Notice the only real work in that function is the actual (-) function. Just subtract your values and format the desired output from there.
0 Likes 0 ·
OracleApprender avatar image
OracleApprender answered

I got the answer for this question. It is MONTHS_BETWEEN function.

SELECT MONTHS_BETWEEN('01-AUG-01','01-JAN-01') AS MONTHS_BETWEEN FROM Dual;

MONTHS_BETWEEN

         7
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.