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
Sun Certified Java Programmer<br />Sun Certified Web Component Developer
Joined: Jul 11, 2001
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;
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
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.
Joined: Jul 11, 2001
Younes, I am sure it will help me in the future. thanks. I did not know about that new feature in 9i. Beksy