Database Reference
In-Depth Information
Table 12-3.
Adding Time to a Date
Unit of Time
Operation
Description
DATE + n/24/60/60
DATE + n/86400
DATE + NUMTODSINTERVAL(n,'second')
N seconds
There are 86,400 seconds in a day. Since adding
1 adds one day, adding 1/86400 adds one second to
a date. I prefer the n/24/60/60 technique over the
1/86400 technique. They are equivalent. An even
more readable method is to use the
NUMTODSINTERVAL
(number to day/second interval) to add N seconds.
DATE + n/24/60
DATE + n/1440
DATE + NUMTODSINTERVAL(n,'minute')
N minutes
There are 1,440 minutes in a day. Adding 1/1440
therefore adds one minute to a
DATE
. An even more
readable method is to use the
NUMTODSINTERVAL
function.
DATE + n/24
DATE + NUMTODSINTERVAL(n,'hour')
N hours
There are 24 hours in a day. Adding 1/24 therefore
adds one hour to a
DATE
. An even more readable
method is to use the
NUMTODSINTERVAL
function.
DATE + n
Simply add N to the
DATE
to add or subtract N days.
N days
DATE + 7*n
N weeks
A week is seven days, so just multiply 7 by the number
of weeks to add or subtract.
ADD_MONTHS(DATE,n)
DATE + NUMTOYMINTERVAL(n,'month')
You may use the
ADD_MONTHS
built-in function or add
an interval of N months to the
DATE
. Please see the
important caveat noted shortly regarding using month
intervals with
DATE
s.
N months
ADD_MONTHS(DATE,12*n)
DATE + NUMTOYMINTERVAL(n,'year')
You may use the
ADD_MONTHS
built-in function with
12*n
to add or subtract N years. Similar goals may
be achieved with a year interval, but please see the
important caveat noted shortly regarding using year
intervals with dates.
N years
In general, when using the Oracle
DATE
type, I recommend the following:
NUMTODSINTERVAL
built-in function to add hours, minutes, and seconds.
•
Use the
•
Add a simple number to add days.
ADD_MONTHS
built-in function to add months and years.
I do not recommend using the
NUMTOYMINTERVAL
function (to add months and years). The reason has to do with
how the functions behave at the months' end.
The
ADD_MONTHS
function treats the end of month days specially. It will, in effect, round the dates for us—if we
add one month to a month that has 31 days and the next month has fewer than 31 days,
ADD_MONTHS
will return the
last day of the next month. Additionally, adding one month to the last day of a month results in the last day of the next
month. We see this when adding one month to a month with 30 or fewer days:
•
Use the
EODA@ORA12CR1> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
EODA@ORA12CR1> select dt, add_months(dt,1)
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
