How To Add Day, Hour, Minute, Second to a Date Value in Oracle

Date arithmetic is very common in database application. In Oracle, you can add, subtract and compare DATE columns, but you can not multiply or divide it. Oracle stores century, year, month, day, hour, min and seconds as part of the DATE column.


Now let’s take a look at how to add day/hour/minute/second to a date value. Oracle expects a number constant in date arithmetic as number of days. In other words, you need to convert hour, minute and seconds to fraction of a day and then you can add or subtract that value from a date value. Here are some examples:


Add a day.
select to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') today,
to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss')+1 next_day
from dual;
TODAY NEXT_DAY
------------------------- -------------------------
02-22-08 10:30:30 02-23-08 10:30:30
Add an hour.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/24 next_hour
from dual;
TODAY NEXT_HOUR
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 11:30:30
Add a minute.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60) next_min
from dual;
TODAY NEXT_MIN
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 10:31:30
Add a second.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60*60) next_sec
from dual;
TODAY NEXT_SEC
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 10:30:31


Subtract a day.
select to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') today,
to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') - 1 prev_day
from dual;
TODAY PREV_DAY
------------------------- -------------------------
02-22-08 10:30:30 02-21-08 10:30:30
Subtract an hour.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/24 prev_hour
from dual;
TODAY PREV_HOUR
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 09:30:30
Subtract a minute.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/(24*60) prev_min
from dual;
TODAY PREV_MIN
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 10:29:30
Subtract a second.
select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') - 1/(24*60*60) prev_sec
from dual;
TODAY PREV_SEC
------------------------ ------------------------
02-22-08 10:30:30 02-22-08 10:30:29



Date value in Oracle has two components, date and time. Oracle stores hour, minute and seconds along with date. As you can see from the above examples, date arithmetic in Oracle is simple and easy to use.

  • Issue by:nilesh doshi
  • Web:http://
  • About Viv-Media|Free Add URL|Submit Press Release|Submit How To|SiteMap|Advertise with Us|Help|Contact Viv-Media |China Viv-Media
  • Copyright© 2010-2020 viv-media.com Corporation.
    Use of this web constitutes acceptance of Terms of Service and Privacy Policy. All rights reserved.  Poetry Online :Ancient Chinese Poetry