Round will rounds the date to which it was equal to or greater than the
given date.
Syntax: round (date, (day |
month | year))
If the second parameter was year
then round will checks the month of the given date in
the following ranges.
JAN -- JUN
JUL -- DEC
If the month falls between JAN and JUN then it returns the first day of
the current year.
If the month falls between JUL and DEC then it returns the first day of
the next year.
If the second parameter was month
then round will checks the day of the given date in
the following ranges.
1 -- 15
16 -- 31
If the day falls between 1 and 15 then it returns the first day of the
current month.
If the day falls between 16 and 31 then it returns the first day of the
next month.
If the second parameter was day
then round will checks the week day of the given date
in the following ranges.
SUN -- WED
THU -- SUN
If the week day falls between SUN and WED then it returns the previous
sunday.
If the weekday falls between THU and SUN then it returns the next sunday.
Ø If the second
parameter was null then it returns nothing.
Ø If the you are
not specifying the second parameter then round will resets the time to the begining
of the current day in case of user specified date.
Ø If the you are
not specifying the second parameter then round will resets the time to the begining
of the next day in case of sysdate.
Ex:
SQL> select round(to_date('24-dec-04','dd-mon-yy'),'year'),
round(to_date('11-mar-
06','dd-mon-yy'),'year') from
dual;
ROUND(TO_
ROUND(TO_
------------ ---------------
01-JAN-05 01-JAN-06
SQL> select round(to_date('11-jan-04','dd-mon-yy'),'month'),
round(to_date('18-
jan-04','dd-mon-yy'),'month')
from dual;
ROUND(TO_
ROUND(TO_
------------- ---------------
01-JAN-04 01-FEB-04
SQL> select round(to_date('26-dec-06','dd-mon-yy'),'day'),
round(to_date('29-dec-
06','dd-mon-yy'),'day') from
dual;
ROUND(TO_
ROUND(TO_
-------------- --------------
24-DEC-06 31-DEC-06
SQL> select to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon
yyyy
hh:mi:ss am') from dual;
TO_CHAR(ROUND(TO_DATE('
---------------------------------
24 dec 2006
12:00:00 am
