Round and truncate dates in Oracle

Oracle's TRUNC and ROUND functions apply to date and time values as well as to numbers. Here are some examples of date manipulation using these Oracle functions.

We usually think of rounding as applying only to numeric values: If the digit to the right of where you want to round is 0-4, round down; if it's 5-9, round up. In Oracle, the ROUND function is used for this: The expression ROUND(12.34, 1) evaluates to 12.3, and the expression ROUND(12.37, 1) evaluates to 12.4. Likewise, the TRUNC function truncates instead of rounding. Both TRUNC(12.34, 1) and TRUNC(12.37, 1) evaluate to 12.3.

But you can also apply ROUND and TRUNC to date expressions, giving you the ability to round and truncate to a specified date boundary. What is returned is another date value, but it is adjusted to the requested boundary. To specify the boundary, use the same format codes used with the TO_CHAR and TO_DATE functions: 'dd' for day, 'mon' for month, 'q' for quarter, and 'y' for year. You can also round time the same way: use 'hh' or 'hh24' for hours and 'mi' for minutes.

Listing A is a sample report from the EMPLOYEES table in Oracle's sample HR schema. For each employee, it shows the employee name and exact hire date, the first day of the month in which the employee was hired (hire_month), and it calculates the start of the following month (benefits_date).

col employee_name format a30
col hire_date format a15
col hire_month format a15
col benefits_date format a15

    last_name || ', ' || first_name employee_name


    ,TRUNC(hire_date, 'mon') hire_month

    ,ADD_MONTHS(TRUNC(hire_date, 'mon'), 1) benefits_date




    last_name LIKE 'B%'




The ADD_MONTHS function is used here to round up to the following month. First, I use TRUNC to get the beginning of the current month; then I use ADD_MONTHS to add one month. The resulting expression is:

ADD_MONTHS(TRUNC(hire_date,'mon'), 1)

When using DBMS_JOB to schedule batch jobs (vs. DBMS_SCHEDULER in Oracle 10g), TRUNC is a handy way to force a job to start at a particular time. Because the SYSDATE function returns both a date and a time, a job scheduled as 'SYSDATE+1' is scheduled for tomorrow but at the current time. TRUNC strips away the decimal portion, which represents the time and resets the date to midnight. So TRUNC(SYSDATE) is midnight of the current day, and TRUNC(SYSDATE)+1 is midnight of the following day. Adding a fractional offset to this sets the proper time. The expression TRUNC(SYDATE) + 1 + 6/24 + 30/1440 starts the job at 6:30 A.M. (There are 24 hours in a day and 1440 minutes.) View the output.

In Oracle 10g, these date calculations are no longer necessary: DBMS_SCHEDULER uses a more direct approach to indicating the time a job is to be run.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.

Get Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!