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.
Answer by HillbillyToad ·
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; /
Answer by coskan ·
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;
call it like
SQL> select datediff('hh',sysdate-50,sysdate) from dual 2 ;