--> ok, we want to make a function that will give the time difference in
minutes

create table date_compare (
date1 date,
date2 date
);

--> insert 15 minutes ahead
insert into date_compare values (sysdate, sysdate+15/1440);

commit;

--> run a query to see what the difference was.
set linesize 200;
select to_char(date1,'dd-mon-yyyy hh24:mi:ss') date1,
to_char(date2,'dd-mon-yyyy hh24:mi:ss') date2,
date2-date1 date_difference,
(date2-date1)*24 hour_difference,
(date2-date1)*1440 minute_difference,
(date2-date1)*86400 second_difference
from date_compare;

--> now just write a function to do what the select statement did... A
very
--> simple example would be (note: it NEEDS to be smarter than this):
create or replace function time_difference_minutes (
date1_in in date,
date2_in in date
) return number is

v_difference number;
begin
if date2_in > date1_in then
v_difference := (date2_in - date1_in)*1440;
else
v_difference := (date1_in - date2_in)*1440;
end if;

return v_difference;
exception
when others then
return null;
end;
/

--> finally
select time_difference_minutes (date1, date2) difference_in_minutes
from date_compare;

>>> "Juan le Roux "
<mssqldba-ezmlmshield-x10149791.[Email address protected] 3/30/2005
12:19:38 AM >>>
Hi

I created a table that stores date fields, I must write functionality
that
only gets the time differense between each date fields, not the day,
here is
example:

date1 := 2005-03-25 10:00:00 and date2 := 2005-03-28 13:00:00

The result I want is in hours this would be: 3 hours, at the moment I
am
getting the days included which is: 75 hours

Please help

Thanks
Juan
Juan Le Roux
Software Engineer
IFS Defence SA (Pty) Ltd

E-Mail: <<mailto:juan.[Email address protected]
Switchboard: +27-(0)12-663-5350 Fax: +27-(0)12-663-5370
Direct Tel: +27-(0)12-663-5350 Cell: +27-(0)83-670-7851
Web-Site: <<http://www.ifsdefence.com/>>
Address: P.O. Box 9984, Centurion, 0046, South Africa
DISCLAIMER NOTICE:
This e-mail and any associated files are confidential and intended only
for
the addressee named above. If you are not the intended recipient or
the
person responsible for delivering to the intended recipient, be advised
that
you have received this email in error and you should delete it from
your
system. Any views or opinions presented are solely those of the sender
and
do not necessarily represent those of IFS Defence SA (Pty) Ltd unless
otherwise specifically stated. As internet communications are not
secure we
do not accept legal responsibility for the contents of this message
nor
responsibility for any change made to this message after it was sent by
the
original sender. We advise you to carry out your own virus check
before
opening any attachment, as we cannot accept liability for any damage
sustained as a result of any software viruses.
If you have received this email in error, or if you are concerned with
its
content or attachments, please notify The IT Services Manager:
lucio.[Email address protected]




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page