All Products
Search
Document Center

ROUND (date)

Last Updated: Jun 18, 2021

The ROUND(date) function returns a date and time value that is nearest to the specified date. The fmt parameter specifies the unit in which the interval between the return value and the specified date is measured.

Syntax

ROUND(date,[fmt])

Parameters

Parameter

Description

date

A value of the following data types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The values of all these data types include date values.

fmt

The unit in which the interval between the return value and the specified date is measured. The following table lists the valid values of this parameter, which are not case-sensitive.

Values of the fmt parameter

Description

j

The date and time value that corresponds to the nearest 00:00:00 is returned. This is the default value.

day, dy, and d

The nearest Sunday of the specified date is returned.

month, mon, mm, and rm

The first date of the month nearest to the specified date is returned.

q

The first date of the quarter nearest to the specified date is returned.

syear, year, yyyy, yyy, yy, and y

The first date of the year nearest to the specified date. The number of the y letters determines the precision of the interval value.

cc and scc

The first date of the century nearest to the specified date.

Return type

The return type is DATE.

Examples

Execute the following statement:

SELECT SYSDATE Current date,
ROUND(SYSDATE) The date and time value of the nearest 00:00:00,
ROUND(SYSDATE,'DAY') The nearest Sunday,
ROUND(SYSDATE,'MONTH') The first date of the nearest month,
ROUND(SYSDATE,'Q') The first date of the nearest quarter,
ROUND(SYSDATE,'YEAR') The first date of the nearest year 
FROM DUAL;

The following result is returned:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| Current date            | The date and time value of the nearest 00:00:00        | The nearest Sunday          | The first date of the nearest month            | The first date of the nearest quarter        | The first date of the nearest year        |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2020-03-08 20:24:53 | 2020-03-09 00:00:00 | 2020-03-08 00:00:00 | 2020-03-01 00:00:00 | 2020-04-01 00:00:00 | 2020-01-01 00:00:00 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+