Oracle / PLSQL: ROUND Function (with dates)

This article is written about how to use the Oracle/PLSQL ROUND characteristic (as it applies to date values) with syntax and examples.

Description

The Oracle/PLSQL ROUND characteristic returns a date rounded to a precise unit of measure.

ROUND Function Syntax (with dates)

The syntax for the ROUND characteristic in Oracle/PLSQL is:

ROUND( date [, format] )

Parameters or Arguments

date The date to round. format Optional. The unit of measure to observe for rounding. If the structure parameter is omitted, the ROUND function will round to the nearest day. It can be can one of the following values: Unit Valid layout parameters Rounding Rule Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Rounds up on July 1st ISO Year IYYY, IY, I Quarter Q Rounds up on the sixteenth day of the 2nd month of the quarter Month MONTH, MON, MM, RM Rounds up on the sixteenth day of the month Week WW Same day of the week as the first day of the year IW IW Same day of the week as the first day of the ISO year W W Same day of the week as the first day of the month Day DDD, DD, J Start day of the week DAY, DY, D Hour HH, HH12, HH24 Minute MI

Returns

The ROUND feature (as it applies to dates) returns a date value.

Applies To

The ROUND function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example – with dates

Let’s look at some Oracle ROUND function examples and explore how to use the ROUND feature in Oracle/PLSQL.

For example:

ROUND(TO_DATE ('22-AUG-03'),'YEAR')
Result: '01-JAN-04'

ROUND(TO_DATE ('22-AUG-03'),'Q')
Result: '01-OCT-03'

ROUND(TO_DATE ('22-AUG-03'),'MONTH')
Result: '01-SEP-03'

ROUND(TO_DATE ('22-AUG-03'),'DDD')
Result: '22-AUG-03'

ROUND(TO_DATE ('22-AUG-03'),'DAY')
Result: '24-AUG-03'