x

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.

more ▼

asked Jan 05 '10 at 10:01 AM in Default

OracleApprender gravatar image

OracleApprender
771 68 73 75

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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; / 
more ▼

answered Jan 05 '10 at 10:13 AM

HillbillyToad gravatar image

HillbillyToad
1k 2

Thank you for your answer. Do you know any function existed for the same instead of the entire procedure?
Jan 05 '10 at 11:17 AM OracleApprender
No procedure necessary, just subtract the 2 values.
Jan 05 '10 at 12:18 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Jan 05 '10 at 11:54 AM

coskan gravatar image

coskan
21

Notice the only real work in that function is the actual (-) function. Just subtract your values and format the desired output from there.
Jan 05 '10 at 07:46 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Jan 06 '10 at 06:17 AM

OracleApprender gravatar image

OracleApprender
771 68 73 75

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x375
x362
x50

asked: Jan 05 '10 at 10:01 AM

Seen: 2083 times

Last Updated: Jan 05 '10 at 10:01 AM