Date Difference

[Sam Furtado]

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
Thanks

[Beksy Kurian]

Sam,
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
declare
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;
begin
open c1;
loop
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 :=
trunc(end_now-add_months(st_now,months_between(end_now,st_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);
dbms_output.put_line(chr(10)||
'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;
end;

Regards
Beksy

[Mark Spritzler]

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
Mark

[Younes Essouabni]

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 ]

[Beksy Kurian]

Younes,
I am sure it will help me in the future. thanks. I did not know about that new feature in 9i.
Beksy