Re: DateTime Calculations

melrose_2_at_hotmail.com wrote:
>
> We are implementing a GLOBAL application that stores all datetime values
> in GMT. We can NOT use the supplied NEW_TIME function because it only
> covers the Eastern Hemisphere. I always know the GMT HOURLY OFFSET of the
> user attempting to update the database ...MY PROBLEM is ....... How do I
> increment or decrement and ORACLE DATE column by an integer number of
> hours ?? Does any one have a PL/SQL function for doing this ?? ANY help
> in this matter would be GREATLY appreciated !! TIA......
>

Because an hour is 1/24 of a day, you can just add (or substract) the number of hours divided by 24 to the DATE.

e.g.
select to_char(sysdate + 5/24,'DD-MON-YY HH24:MI:SS') from dual;

or:

create or replace function add_hours(olddate in date, hours in number) return date
as
begin
  return olddate + hours / 24;
end;

select add_hours(sysdate,5) from dual;
select add_hours(sysdate,-5) from dual; Received on Fri Dec 05 1997 - 00:00:00 CST