Oracle PL/SQL Tutorial/Date Timestamp Functions/ROUND

Datetime Formatting Parameters for ROUND(x[, unit])

Aspect Parameter Description Example Century CC Two-digit century. 21 Century SCC Two-digit century with a negative sign (C) for B.C. -10 Quarter Q One-digit quarter of the year. 1 Year YYYY All four digits of the year. 2006 Year IYYY All four digits of the ISO year. 2006 Year RRRR All four digits of the rounded year, which depends on the current year. 2006 Year SYYYY All four digits of the year with a negative sign (C) for B.C. C1001 Year Y,YYY All four digits of the year with a comma. 2,006 Year YYY Last three digits of the year. 006 Year IYY Last three digits of the ISO year. 006 Year YY Last two digits of the year. 06 Year IY Last two digits of the ISO year. 06 Year RR Last two digits of the rounded year, which depends on the current year. 06 Year Y Last digit of the year. 6 Year I Last digit of the ISO year. 6 Year YEAR Name of the year in uppercase. TWO THOUSAND-SIX Year Year Name of the year with the first letter in uppercase. Two Thousand-Six Month MM Two-digit month of the year. 01 Month MONTH Full name of the month in uppercase, right-padded with spaces to a total length of nine characters. JANUARY Month Month Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters. January Month MON First three letters of the name of the month in uppercase. JAN Month Mon First three letters of the name of the month with the first letter in uppercase. Jan Month RM Roman numeral month. The Roman numeral month for the fourth month (April) is IV. Week WW Two-digit week of the year. 02 Week IW Two-digit ISO week of the year. 02 Week W One-digit week of the month. 2 Day DDD Three-digit day of the year. 103 Day DD Two-digit day of the month. 31 Day D One-digit day of the week. 5 Day DAY Full name of the day in uppercase. SATURDAY Day Day Full name of the day with the first letter in uppercase. Saturday Day DY First three letters of the name of the day in uppercase. SAT Day Dy First three letters of the name of the day with the first letter in uppercase. Sat Day J Julian day-the number of days that have passed since January 1, 4713 B.C. 2439892 Hour HH24 Two-digit hour in 24-hour format. 23 Hour HH Two-digit hour in 12-hour format. 11 Minute MI Two-digit minute. 57 Second SS Two-digit second. 45 Second FF[1..9] Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123. Second SSSSS Number of seconds past 12 a.m. 46748 Second MS Millisecond (millionths of a second). 100 Second CS Centisecond (hundredths of a second). 10 Separators -/,.;: "text" Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator. When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969 Suffixes AM or PM AM or PM as appropriate. AM Suffixes A.M. or P.M. A.M. or P.M. as appropriate. P.M. Suffixes AD or BC AD or BC as appropriate. AD Suffixes A.D. or B.C. A.D. or B.C. as appropriate. B.C. Suffixes TH Suffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH Suffixes SP Number is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight Suffixes SPTH Combination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth Era EE Full era name for Japanese Imperial, ROC Official, and Thai Buddha calendars. No example Era E Abbreviated era name. No example Time zones TZH Time zone hour. 12 Time zones TZM Time zone minute. 30 Time zones TZR Time zone region. PST Time zones TZD Time zone with daylight savings information. No example

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

SQL>
SQL>
SQL> select sysdate
  2  ,      round(sysdate,"YY")     as round_yy
  3  from   dual;
SYSDATE   ROUND_YY
--------- ---------
24-JUL-08 01-JAN-09
SQL>

ROUND(date_in_1,"CC")

SQL>
SQL> DECLARE
  2     date_in_1 DATE := TO_DATE("24-Feb-2002","DD-MON-YYYY");
  3     date_in_2 DATE := TO_DATE("24-Feb-1902","DD-MON-YYYY");
  4     date_in_3 DATE := TO_DATE("24-Feb-2002 05:36:00 PM","DD-MON-YYYY HH:MI:SS AM");
  5
  6     round_1 DATE;
  7     round_2 DATE;
  8     round_3 DATE;
  9
 10  BEGIN
 11     round_1 := ROUND(date_in_1,"CC");
 12     round_2 := ROUND(date_in_2,"CC");
 13     round_3 := ROUND(date_in_3,"HH");
 14
 15     DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_1,"DD-MON-YYYY HH:MI:SS AM"));
 16     DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_2,"DD-MON-YYYY HH:MI:SS AM"));
 17     DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_3,"DD-MON-YYYY HH:MI:SS AM"));
 18  END;
 19  /
PL/SQL procedure successfully completed.
SQL>

Round date to day

SQL>
SQL> SELECT ROUND(TO_DATE("25-OCT-2005"), "DD") FROM dual;
ROUND(TO_
---------
25-OCT-05

Rounding to the Nearest Minute

SQL> SELECT TO_CHAR(ROUND(TO_DATE("060299 01:00:35 AM",
  2            "MMDDYY HH:MI:SS AM"),
  3            "MI"), "DD-MON-YY HH:MI:SS AM") "Rounded to nearest Minute"
  4       from DUAL;
Rounded to nearest Mi
---------------------
02-JUN-99 01:01:00 AM
SQL>
SQL>

Round result from months_between

SQL>
SQL>
SQL> select round(months_between("17-MAR-61","21-APR-62"))
  2  from dual;

ROUND(MONTHS_BETWEEN("17-MAR-61","21-APR-62"))
----------------------------------------------
                                           -13
1 row selected.
SQL>

Rounds May, to the first day in the nearest month

SQL>
SQL> SELECT ROUND(TO_DATE("25-MAY-2005"), "MM") FROM dual;
ROUND(TO_
---------
01-JUN-05
SQL>

ROUND(x[, unit]) : round x. By default, x is rounded to the beginning of the nearest day

If you supply an optional unit string, x is rounded to that unit;

In the following example, YYYY rounds x to the first day of the nearest year.

SQL>
SQL> SELECT ROUND(TO_DATE("25-OCT-2005"), "YYYY") FROM dual;
ROUND(TO_
---------
01-JAN-06

The ROUND Function

ROUND is very similar to the TRUNC function.

It uses the same format mask as TRUNC.

ROUND rounds up or down based upon the format mask.

The default mask when specifying a DATE value is DD.

Some useful purposes for this are

1. Rounding to the nearest minute for billing cellular-based calls
2. Rounding to closest month to determine a pay period

The Syntax for the ROUND Function

ROUND(input_date and time or number,rounding_specification)