ROUND (datetime)

Purpose

This function rounds a date or timestamp value to the specified unit.

Syntax

round (datetime)::=

Usage Notes

  • The following elements can be used as format:

    • CC, SCC Century
    YYYY, SYYY, YEAR, SYEAR, YYY, YY, Y Year
    IYYY, IYY, IY, I Year in accordance with international standard, ISO 8601
    Q Quarter
    MONTH, MON, MM, RM Month
    WW Same day of the week as the first day of the year
    IW Same day of the week as the first day of the ISO year
    W Same day of the week as the first day of the month
    DDD, DD, J Day
    DAY, DY, D Starting day of the week. The first day of a week is defined by the parameter NLS_FIRST_DAY_OF_WEEK (refer to ALTER SESSION and ALTER SYSTEM).
    HH, HH24, HH12 Hour
    MI Minute
    SS Seconds

  • Rounding-up is as follows: years from July 1, months from the 16th of a month, days from noon, hours from 30 minutes, minutes from 30 seconds and seconds from 500 milliseconds. Otherwise, values are rounded down.
  • If a format is not specified, the value is rounded to days.
  • For data type TIMESTAMP WITH LOCAL TIME ZONE this function is calculated within the session time zone.

Example

SELECT ROUND(DATE '2006-12-31', 'YYYY') ROUND;

SELECT ROUND(TIMESTAMP '2006-12-31 12:34:58', 'MI') ROUND;