(if that email address didn't require changing)
>I'm converting a report generating application from using SQL Server to
>Oracle. The SQL contains a call to the DateDiff function and selecting
>only rows where the difference is under a specified time.
>It seems that Oracle 7.3.4 is really lean on the date (and string)
>functions it provides. I looked at MONTHS_BETWEEN, but for my
>application it will allow too wide of a range of dates. This is the
>only function I saw that calculates a difference.
>If you know of more date functions, please let me know.
SQL> set serveroutput on
2 a date;
3 b date;
5 a := sysdate;
6 dbms_lock.sleep(10); -- sleep about 10 seconds give or take
7 b := sysdate;
9 dbms_output.put_line( b-a || ' of a day has elapsed' );
10 dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
11 dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
12 dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
.000127314814814814814814814814814814814815 of a day has elapsed
.00305555555555555555555555555555555555556 of an hour has elapsed
.1833333333333333333333333333333333333336 of a minute has elapsed
11.00000000000000000000000000000000000002 seconds has elapsed
PL/SQL procedure successfully completed.
date arithmetic is so trivial that a specialized function like datediff is not
needed. Just subtract. You get the difference in days. Multiply by 24 --
hours, multiply by 60 minutes, multiply by 60 -- seconds.
If you want to do it in SQL:
select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi",
trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
where rownum < 50
Dy gives you number of days between 2 dates (partial days discarded). Tdy gives
you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days)
Hr/Thr = hours
Mi/Tmi = minutes
Sec/Tsec = seconds...
If you really want 'datediff' in your database, you can just do something like
SQL> create or replace function datediff( p_what in varchar2,
2 p_d1 in date,
3 p_d2 in date ) return number
5 l_result number;
7 select (p_d2-p_d1) *
8 decode( upper(p_what),
9 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
10 into l_result from dual;
11 return l_result;
Now, i just create a view to demonstrate with:
SQL> create or replace view temp_view
3 select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,
4 to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2
5 from dual
SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;
SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;
SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;
>In the meantime I'm trying to write my own function. I was hoping that
>by assigning the date to a long, the date would be 'implicitly'
>transformed to a long, then I could do arithmetic on the dates to get
>the difference. Unfortunately what seemed to happen is that my longs
>got transformed into dates and I could no longer do arithmetic on them
>although they did print out nicely in the default time format (argghh).
>Also, from the lack of documentation about arrays, I'd have to assume
>that PL/SQL doesn't allow for arrays aside from those meant for row
they are not called arrays, they are called collection types and plsql table
types. Here is an example:
2 type myArray is table of number index by binary_integer;
3 x myArray;
5 for i in 1 .. 10 loop
6 x(i) := i;
7 end loop;
8 for i in 1 .. x.count loop
9 dbms_output.put_line( x(i) );
10 end loop;
PL/SQL procedure successfully completed.
Quote:>Does anyone think that PL/SQL is good? :)
I myself think it is awesome (especially compared to say TSQL :)
Its got alot of attributes of 3gls, packages are really great (session
persistent variables, hidden functions, the ability to write really big modular
programs, etc), dynamic sql, cursor variables, etc etc etc....
Once you learn it, you'll wonder how you ever managed with just tsql to code
>ps: sorry if this got posted twice, but my usual new server seems to not
>be working well.
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation