ROUND(date) Function in Oracle

In Oracle, the ROUND(date) function returns a date rounded to a specified date unit.

By default, it rounds the date to the nearest day, but you can provide an optional argument that specifies an alternative unit to use.

Oracle also has a ROUND(number) syntax, which is used on number. This article is solely about the ROUND(date) syntax, which is used on date.

Syntax

The syntax goes like this:

ROUND(date [, fmt ])

Where date must resolve to a DATE value, and fmt is an optional format model that specifies the unit to round date to. The format model can be any of the supported format models for the TRUNC(date) and ROUND(date) functions.

Example

Here’s an example:

SELECT 
    ROUND(TO_DATE('2030-12-31 12:30:45', 'YYYY-MM-DD HH24:MI:SS'))
FROM DUAL;

Result:

01-JAN-31

In this case, the time portion was past midday, and the date was rounded to the next day (which also happened to be the next month and year).

It was rounded to the day because that’s the default unit to round to, and we didn’t explicitly specify a different unit.

Here’s what happens when I reduce the time portion to before midday:

SELECT 
    ROUND(TO_DATE('2030-12-31 11:59:59', 'YYYY-MM-DD HH24:MI:SS'))
FROM DUAL;

Result:

31-DEC-30

This time it’s rounded to the same day.

Note that the date format returned depends on the value of your NLS_DATE_FORMAT parameter (here’s how to format date values for your session if you’re interested).

Round to a Specified Date Unit

Here’s an example of specifying a different date unit to round the date to:

SELECT 
    ROUND(DATE '2030-12-31', 'MONTH')
FROM DUAL;

Result:

01-JAN-31

Here it is again, but with various other dates:

SELECT 
    ROUND(DATE '2030-03-10', 'MONTH') AS "2030-03-10",
    ROUND(DATE '2030-03-18', 'MONTH') AS "2030-03-18",
    ROUND(DATE '2030-08-10', 'MONTH') AS "2030-08-10",
    ROUND(DATE '2030-08-10', 'MONTH') AS "2030-08-10"
FROM DUAL;

Result:

   2030-03-10    2030-03-18    2030-08-10    2030-08-10 
_____________ _____________ _____________ _____________ 
01-MAR-30     01-APR-30     01-AUG-30     01-AUG-30

And here it is with the same date, but various format elements:

SELECT 
    ROUND(DATE '2030-10-31', 'DAY') AS "Day",
    ROUND(DATE '2030-10-31', 'WW') AS "Week of year",
    ROUND(DATE '2030-10-31', 'MONTH') AS "Month",
    ROUND(DATE '2030-10-31', 'YEAR') AS "Year"
FROM DUAL;

Result:

         Day    Week of year        Month         Year 
____________ _______________ ____________ ____________ 
03-NOV-30    29-OCT-30       01-NOV-30    01-JAN-31

This demonstrates how much variation we can have, depending on the date unit specified.

Negative Date

Here’s what happens when we turn them into negative dates:

SELECT 
    ROUND(DATE '-2030-10-31', 'DAY') AS "Day",
    ROUND(DATE '-2030-10-31', 'WW') AS "Week of year",
    ROUND(DATE '-2030-10-31', 'MONTH') AS "Month",
    ROUND(DATE '-2030-10-31', 'YEAR') AS "Year"
FROM DUAL;

Result:

         Day    Week of year        Month         Year 
____________ _______________ ____________ ____________ 
28-OCT-30    29-OCT-30       01-NOV-30    01-JAN-29

Obviously, the results will change as the date changes.

Rounding Non-Date Arguments

Here’s what happens when we try to round a non-date argument that can’t be converted to a DATE data type:

SELECT ROUND('Bruce')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND('Bruce')
FROM DUAL
Error report -
ORA-01722: invalid number

However, we can round numbers – there’s a numeric version of this function that allows us to round numbers.

Null Values

Trying to round null returns null, and trying to round a date by null also results in null:

SET NULL 'null';

SELECT 
    ROUND(null),
    ROUND(null, 'MONTH'),    
    ROUND(DATE '2030-12-20', null)
FROM DUAL;

Result:

   ROUND(NULL)    ROUND(NULL,'MONTH')    ROUND(DATE'2030-12-20',NULL) 
______________ ______________________ _______________________________ 
          null                   null null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling ROUND() without passing any arguments returns an error:

SELECT ROUND()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT ROUND(DATE '2030-12-20', 'day', 'month')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND(DATE '2030-12-20', 'day', 'month')
FROM DUAL
Error at Command Line : 1 Column : 40
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: