Oracle SQL: the Essential Reference

ROUND

ROUND(d [,fmt])

Rounds a date/time value to the nearest date/time unit specified.

Parameters

d

Specifies a date/time value.

fmt

Specifies a date format element. See Table 5-4 for a complete list. The input value will be rounded to the unit specified by this format element. Rounding a date/time value to the nearest day is the default.

Rounding is not the same as truncating. Rounding a date may result in a new date that is greater than the date you started with. If you don't want to round up, use the TRUNC function instead.

Example

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY hh24:mi';

Session altered.

SQL> SELECT ROUND(TO_DATE('21-Jul-2000 15:20')) day,
  2         ROUND(TO_DATE('21-Jul-2000 15:20'),'HH') hour,
  3         ROUND(TO_DATE('21-Jul-2000 15:20'),'YYYY') year
  4  FROM dual;

DAY               HOUR              YEAR
----------------- ----------------- -----------------
22-Jul-2000 00:00 21-Jul-2000 15:00 01-Jan-2001 00:00

Table 5-4. Date Format Elements

Format Element	Function
- / , . ; :	Punctuation may be included anywhere in the date format string, and will be included in the output.
`text'	Quoted text may also be included in the date format string, and will be reproduced in the output.
AD or A.D. BC or B.C.	Includes an A.D. or B.C. indicator with the date.
AM or A.M. PM or P.M.	Prints AM or PM, whichever applies to the time.
CC	The century number. This number is 20 for years 1900 through 1999. ...