PL/SQL ROUND date

The Oracle PL/SQL ROUND function is a built-in function that is used to round numerical values to a specified number of decimal places. However, it can also be used to round dates to a specific date format.

Syntax

When rounding a date using the ROUND function, the date is rounded to the nearest specified unit, such as year, month, day, hour, or minute. The syntax for using the ROUND function to round a date is as follows:

ROUND(date [, fmt])

The date parameter represents the date value that needs to be rounded, while the fmt parameter is an optional parameter that specifies the date format to which the date needs to be rounded.

The fmt parameter can take the following values:

YYYY: Rounds the date to the nearest year.
MM: Rounds the date to the nearest month.
DD: Rounds the date to the nearest day.
HH: Rounds the date to the nearest hour.
MI: Rounds the date to the nearest minute.

YEAR: Rounds to the nearest year
MONTH: Rounds to the nearest month
DAY: Rounds to the nearest day

Example

For example, suppose we have a date value of ‘2022-06-17 14:32:12’. If we want to round this value to the nearest minute, we can use the following SQL statement:

SELECT 
ROUND(TO_DATE('2022-06-17 14:32:12', 'YYYY-MM-DD HH24:MI:SS'), 'MI') 
FROM dual;

The output of this statement will be ‘2022-06-17 14:32:00’, which represents the rounded date value.

select ROUND(sysdate, 'YYYY') from dual;
select ROUND(sysdate, 'MM') from dual;
select ROUND(sysdate, 'DD') from dual;
select ROUND(sysdate, 'HH') from dual;
select ROUND(sysdate, 'MI') from dual;

select ROUND(sysdate, 'YEAR') from dual;
select ROUND(sysdate, 'MONTH') from dual;
select ROUND(sysdate, 'DAY') from dual;

In conclusion, the Oracle PL/SQL ROUND function can be used to round a date to a specific date format by specifying the desired unit of rounding in the optional fmt parameter. It is easy to use and can be incorporated into SQL queries and stored procedures to perform date calculations and manipulations.