• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Date Difference

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 479
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Younes,
I am sure it will help me in the future. thanks. I did not know about that new feature in 9i.
Beksy
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic