Rounding and Truncating Dates

Rounding and truncating dates is similar in concept to the rounding and truncating of numbers, but more involved because an Oracle DATE contains date as well as time information. Use the ROUND function to round a date/time value to a specific element; use the TRUNC function to truncate a date/time value to a specific ele- ment. Following is the syntax for invoking these two functions:

ROUND(date [, format]) TRUNC(date [, format]) The syntax elements are:

date

Specifies a DATE value. format Specifies the date element to round or truncate to.

The return value depends upon the specified format, which is an optional parame- ter. If we don’t specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If we don’t specify a format in the call to TRUNC, that function returns a date by removing the fractional part of the day.

When using ROUND and TRUNC to round to the nearest day, or to truncate a date, the functions set the time fields of the return value to the beginning of the returned day, i.e., 12:00:00 AM (00:00:00 in HH24 format).

For example:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),     

 TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),      

TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM');