File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Date Difference Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Date Difference" Watch "Date Difference" New topic

Date Difference

Sam Furtado
Ranch Hand

Joined: Jul 16, 2002
Posts: 45
Hi Guys !!!
I have been desperately searching for a DateDiff() in oracle similar in functionality to the one that exists in SQL Server since, here i need to create a query that would retreive records if the current sysdate is greater than the timestamp table field by - 1 Hour, 1 Day, 1Week. How should the same be done in Oracle.
Pls let me know

Sun Certified Java Programmer<br />Sun Certified Web Component Developer
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
There are no oracle functions which does the exact work for you. You may try the following to get the years, months, days separately.
select trunc(months_between(to_date('11052002','DDMMYYYY'),to_date('15042000','DDMMYYYY'))/12) as "years",
trunc(months_between(to_date('11052002','DDMMYYYY'),to_date('15042000','DDMMYYYY'))) as "months",
(to_date('11052002','DDMMYYYY')-to_date('15042000','DDMMYYYY') ) as "days"
from dual
I am sure this is not what you were looking for.

Oracle is giving a following pl/sql example to achieve this. Try it out.

create table ddiff
(stdate date,
endate date);
insert into ddiff (stdate,endate) values
(to_date('01-jan-96 00:00:00','dd-mon-yy hh24:mi:ss'),
to_date('01-mar-97 01:01:01','dd-mon-yy hh24:mi:ss'));
insert into ddiff (stdate,endate) values
(to_date('01-jun-99 00:00:00','dd-mon-yy hh24:mi:ss'),
to_date('03-oct-99 02:02:02','dd-mon-yy hh24:mi:ss'));
set echo off
set serverout on
cursor c1 is
select stdate,endate from ddiff;
date_res number;
end_now date;
f_end_now char(20);
st_now date;
temp_now date;
f_st_now char(20);
yy_now number := 0;
mm_now number := 0;
dd_now number := 0;
hh_now number := 0;
f_hh_now number := 0;
mi_now number := 0;
f_mi_now number := 0;
ss_now number := 0;
f_ss_now number := 0;
open c1;
fetch c1 into st_now, end_now;
exit when c1%notfound;
-- Make st_now the earlier date
if (end_now < st_now) then
temp_now := end_now;
end_now := st_now;
st_now := temp_now;
end if;
f_end_now := to_char(end_now,'Mon-dd-yyyy hh24:mi:ss');
f_st_now := to_char(st_now,'Mon-dd-yyyy hh24:mi:ss');
yy_now := trunc(months_between(end_now,st_now)/12);
mm_now := mod(trunc(months_between(end_now,st_now)),12);
dd_now :=
date_res := (end_now-st_now);
hh_now := (date_res-floor(date_res))*24;
f_hh_now := trunc(hh_now);
mi_now := (hh_now-floor(hh_now))*60;
f_mi_now := trunc(mi_now);
ss_now := (mi_now-floor(mi_now))*60;
f_ss_now := round(ss_now);
'The time difference between ');
dbms_output.put_line('starting date '||f_st_now);
dbms_output.put_line('and ending date '||
f_end_now||' is');
dbms_output.put_line(chr(10)||yy_now||' year(s), '||
mm_now||' month(s), '||dd_now||' day(s)');
dbms_output.put_line('and '||f_hh_now||' hour(s), '||
f_mi_now||' minute(s), '||
f_ss_now||' second(s)');
end loop;
close c1;

Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

Sam, have you tried just subtracting the two dates?
If the result is in seconds or milliseconds, you will have to divide that result by
milliseconds = divide by 1000 to get to seconds then divide by 60 to get to minutes then divide by 60 to get to hours then divide by 24 to get to days, make sure you get the INT and ignore the remainders
There are also to_number functions that can change the dates. like
to_number(DATE,'DDD') which is day number in the year, or maybe 'DD' get that. A good book on Oracle SQL will have all the possible values you can use.
Good Luck

Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
Younes Essouabni
Ranch Hand

Joined: Jan 13, 2002
Posts: 479
With Oracle 9i, there is the feature
Extract (From sybex Introduction to Oracle 9i)
Extract (<c> From <dt>) extracts and returns the specified component c of date/time or interval expression dt. The valid components are Year,Month,Day,Hout,Min,Second,Timezone_hour,Timezone_minute,Timezone_region and timezone_abbr. The specified component must exists in the expression.So to extract a Timezone_hour, the date/time expression must be a Timesatmp with time zone datatype.This function is new to Oracle 9i.

If quoting a book is not allowed, please delete the post.
You may substract two dates with Oracle too.
Good luck.
I hope it helped you.
[ September 09, 2002: Message edited by: Younes Essouabni ]

By constantly trying one ends up succeeding. Thus: the more one fails the more one has a chance to succeed.
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
I am sure it will help me in the future. thanks. I did not know about that new feature in 9i.
I agree. Here's the link:
subject: Date Difference
It's not a secret anymore!