Which version of Oracle do you have? Oracle9i includes many more built-ins for dates and times than earlier versions of Oracle.

I wrote a PL\SQL function some years ago that works with Oracle7 and 8 to calculate the time between two dates. I'll post that here, but it does not round to the nearest 1/4 hour. You would need to modify it to get that effect. I think if you divide the hours by 60 (to get the number of minutes), then add 7.5 minutes(to get the effect of rounding up to the nearest quarter hour), then divide by 15, rounded to 0 (to get the number of quarter hours) then divide that by four, rounded to two digits, you should get the number of hours to the nearest quarter hour.

create or replace function time_between (start_tm in date, end_tm in date,

hours_only varchar2 default 'N') return varchar2 as

-- If "hours_only" is null or "N", the return will be a string formatted like:

-- 2 days, 3 hrs, 5 mins, 10 secs

-- If "hours_only" is not "N", then the return is a value in hours, like 102.325

ret_val varchar2(80);

start_sec number;

end_sec number;

full_sec number;

balance number;

minutes number;

hours number;

days number;

--

function get_sec (time_in in date) return number as

begin

return to_number(to_char(time_in,'SSSSS'));

end;

--

begin

start_sec := get_sec(start_tm);

end_sec := get_sec(end_tm);

-- check if end time is in the same day as start time

if to_char(start_tm,'YYMMDD') = to_char(end_tm,'YYMMDD') then

full_sec := end_sec - start_sec;

days := 0;

else

days := trunc(end_tm - start_tm);

if days > 0 then

ret_val := to_char(days)||' days, ';

end if;

if end_sec > start_sec then

full_sec := end_sec - start_sec;

else

full_sec := 86400 - start_sec + end_sec;

end if;

end if;

if upper(hours_only) = 'N' then

if full_sec > 3599 then

hours := floor(full_sec / 3600);

balance := mod(full_sec,3600);

full_sec := balance;

if hours > 1 then

ret_val := ret_val || to_char(hours) ||' hrs, ';

else

ret_val := ret_val || to_char(hours) ||' hr, ';

end if;

end if;

if full_sec > 59 then

minutes := floor(full_sec / 60);

balance := mod(full_sec,60);

full_sec := balance;

if minutes > 1 then

ret_val := ret_val||to_char(minutes)||' mins, ';

else

ret_val := ret_val||to_char(minutes)||' min, ';

end if;

end if;

ret_val := ret_val||to_char(full_sec)||' secs';

else

-- Calculate the time difference in hours, to three decimal places

ret_val := to_char((24 * days) + round((full_sec / 3600),3));

end if;

return ret_val;

end;

/

grant execute on time_between to public;

create public synonym time_between for time_between;

I wrote a PL\SQL function some years ago that works with Oracle7 and 8 to calculate the time between two dates. I'll post that here, but it does not round to the nearest 1/4 hour. You would need to modify it to get that effect. I think if you divide the hours by 60 (to get the number of minutes), then add 7.5 minutes(to get the effect of rounding up to the nearest quarter hour), then divide by 15, rounded to 0 (to get the number of quarter hours) then divide that by four, rounded to two digits, you should get the number of hours to the nearest quarter hour.

create or replace function time_between (start_tm in date, end_tm in date,

hours_only varchar2 default 'N') return varchar2 as

-- If "hours_only" is null or "N", the return will be a string formatted like:

-- 2 days, 3 hrs, 5 mins, 10 secs

-- If "hours_only" is not "N", then the return is a value in hours, like 102.325

ret_val varchar2(80);

start_sec number;

end_sec number;

full_sec number;

balance number;

minutes number;

hours number;

days number;

--

function get_sec (time_in in date) return number as

begin

return to_number(to_char(time_in,

end;

--

begin

start_sec := get_sec(start_tm);

end_sec := get_sec(end_tm);

-- check if end time is in the same day as start time

if to_char(start_tm,'YYMMDD')

full_sec := end_sec - start_sec;

days := 0;

else

days := trunc(end_tm - start_tm);

if days > 0 then

ret_val := to_char(days)||' days, ';

end if;

if end_sec > start_sec then

full_sec := end_sec - start_sec;

else

full_sec := 86400 - start_sec + end_sec;

end if;

end if;

if upper(hours_only) = 'N' then

if full_sec > 3599 then

hours := floor(full_sec / 3600);

balance := mod(full_sec,3600);

full_sec := balance;

if hours > 1 then

ret_val := ret_val || to_char(hours) ||' hrs, ';

else

ret_val := ret_val || to_char(hours) ||' hr, ';

end if;

end if;

if full_sec > 59 then

minutes := floor(full_sec / 60);

balance := mod(full_sec,60);

full_sec := balance;

if minutes > 1 then

ret_val := ret_val||to_char(minutes)|

else

ret_val := ret_val||to_char(minutes)|

end if;

end if;

ret_val := ret_val||to_char(full_sec)

else

-- Calculate the time difference in hours, to three decimal places

ret_val := to_char((24 * days) + round((full_sec / 3600),3));

end if;

return ret_val;

end;

/

grant execute on time_between to public;

create public synonym time_between for time_between;