Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-10. Rounding a Datetime Value

Problem

Given a particular date and time, you want the ability to round the date.

Solution

Use the ROUND function passing the date you want to round along with the format model for the unit you want to round. For example, suppose that given a date and time, you want to the nearest day. To do this, you would pass in the date along with the DD format model. The following code block demonstrates this technique:

BEGIN   DBMS_OUTPUT.PUT_LINE(to_char(ROUND (SYSDATE, 'DD'),'MM/DD/YYYY - HH12:MI:SS'));  END;

The previous code block will return the current date and time rounded to the nearest day. For example, if it is before 12 p.m., then it will round the given date back to 12 a.m. on that date; otherwise, it will round ...