Oracle Date/Time Function - Oracle/PLSQL ROUND Function date






This Oracle tutorial explains how to use the Oracle/PLSQL ROUND function applying to date values.

The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.

ROUND(x [, unit]) rounds x. By default, x is rounded to the beginning of the nearest day. unit is optional and indicates the rounding unit. For example, YYYY rounds x to the first day of the nearest year.

Syntax

The syntax for the Oracle/PLSQL ROUND function is:

ROUND( date, [ format ] )

format parameters:

ParameterDescription
CCTwo-digit century.
SCCTwo-digit century with a negative sign (-) for B.C.
QOne-digit quarter of the year. (1, 2, 3, 4; JAN-MAR = 1)
YYYYAll 4 digits of the year.
IYYYAll 4 digits of the ISO year.
RRRRAll 4 digits of the rounded year (governed by the present year).
SYYYYAll 4 digits of the year with a negative sign (-) for B.C.
Y,YYYAll 4 digits of the year, with a comma after the first digit.
YYYLast 3 digits of the year.
IYYLast 3 digits of the ISO year.
YYLast 2 digits of the year.
IYLast 2 digits of the ISO year.
RRLast two digits of the rounded year, which depend on the present year.
YLast digit of the year.
ILast digit of the ISO year.
YEARName of the year in uppercase.
YearName of the year with the first letter in uppercase.
MMTwo-digit month of the year. (01-12; JAN = 01)
MONTHFull name of the month in uppercase, right-padded with spaces to a total length of nine characters.
MonthFull name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters.
MONFirst three letters of the name of the month in uppercase.
MonFirst three letters of the name of the month with the first letter in uppercase.
RMRoman numeral month. (I-XII; JAN = I)
WWTwo-digit week of the year. (1-53)
IWTwo-digit ISO week of the year.(1-52 or 1-53)
WOne-digit week of the month.(1-5)
DDDThree-digit day of the year. (1-366)
DDTwo-digit day of the month.(1-31)
DOne-digit day of the week.(1-7)
DAYFull name of the day in uppercase.
DayFull name of the day with the first letter in uppercase.
DYFirst three letters of the name of the day in uppercase.
DyFirst three letters of the name of the day with the first letter in uppercase.
JJulian day-the number of days that have passed since January 1, 4713 B.C.
HH24Two-digit hour in 24-hour format. (0-23)
HH12Hour of day.(1-12)
HHTwo-digit hour in 12-hour format.(1-12)
MITwo-digit minute. (0-59)
SSTwo-digit second.(0-59)
FF[1..9]Fractional seconds.
SSSSSNumber of seconds past 12 A.M. (0-86399)
MSMillisecond (millionths of a second).
CSCentisecond (hundredths of a second).
-/,.;:"text"Characters that allow you to separate the aspects of a date and time.
AM or PM A.M. or P.M. AD or BCAM or PM A.M. or P.M. AD or BC
SPNumber is spelled out
SPTHCombination of SP and TH
EEFull era name
EAbbreviated era name
TZHTime zone hour.
TZMTime zone minute.
TZRTime zone region.
TZDTime zone with daylight savings information.

The following table illustrates some TO_CHAR date formatting:

FormatOutput
dd Month yyyy05 March 2012
dd month YY05 march 12
dd Mon05 Mar
dd RM yyyy05 III 2012
Day Mon yyyySunday Mar 2012
Day fmMonth dd, yyyySunday March 5, 2012
Mon ddsp yyyyMar five 2012
ddMon yy hh24:mi:ss05Mar 12 00:00:00
MONTH DD, YYYYFEBRUARY 05, 2012
MM/DD/YYYY02/05/2012
MM-DD-YYYY02-05-2012
DD/MM/YYYY05/02/2012
DAY MON, YY ADMONDAY FEB, 12 AD
DDSPTH "of" MONTH, YEAR A.D.FIFTH of FEBRUARY, TWENTY TWELVE A.D.
CC, SCC20, 20
Q1
YYYY2012
IYYY2012
RRRR2012
SYYYY2012
Y,YYY2,012
YYY012
IYY012
YY12
IY12
RR12
Y2
I2
YEARTWENTY TWELVE
YearTwenty Twelve
MM02
MONTHFEBRUARY
MonthFebruary
MONFEB
MonFeb
RMII
WW02
IW02
W2
DDD035
DD06
DAYMONDAY,
DayMonday
DYMON
DyMon
J2439999
ddTH05th
DDTH05TH
ddSPfive
DDSPFIVE
DDSPTHFIFTH
HH24:MI:SS19:12:36
HH.MI.SS AM7.12.36 PM




Example


SQL> SELECT ROUND(TO_DATE('25-OCT-2012'), 'YYYY') FROM dual;
-- w ww  . ja v  a 2s  . c  om
ROUND(TO_
---------
01-JAN-13

SQL>
SQL> SELECT ROUND(TO_DATE('25-MAY-2012'), 'MM') FROM dual;

ROUND(TO_
---------
01-JUN-12

SQL>




Rounds to the nearest hour

The following code shows how to round to the nearest hour:


SQL> SELECT TO_CHAR(ROUND(TO_DATE('25-MAY-2008 19:45:26',
  2  'DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS')
  3  FROM dual;-- from  www.  ja v  a 2  s .  com

TO_CHAR(ROUND(TO_DAT
--------------------
25-MAY-2008 20:00:00

SQL>
SQL>