Wednesday, September 16, 2009

pl/sql function to round down to nearest minute interval

create or replace function nearest_min(dt IN DATE, interval_minutes NUMBER)
return DATE
is
newdate DATE;
BEGIN
newdate := trunc(dt,'HH')+(trunc((dt-trunc(dt,'HH'))*24/(interval_minutes/60)))/(60/interval_minutes)/24;
return newdate;
END;
/


Thanks to http://www.freelists.org/post/oracle-l/Fun-with-SYSDATE-TRUNC-and-rounding,1 for the logic behind the calcuation.

No comments:

Post a Comment