How does one add a day/hour/minute/second to a date value?

Submitted by admin on Wed, 2004-08-04 15:16
Body: 

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of examples:

DescriptionDate Expression
NowSYSDATE
Tomorow/ next daySYSDATE + 1
Seven days from nowSYSDATE + 7
One hour from nowSYSDATE + 1/24
Three hours from nowSYSDATE + 3/24
An half hour from nowSYSDATE + 1/48
10 minutes from nowSYSDATE + 10/1440
30 seconds from nowSYSDATE + 30/86400
Tomorrow at 12 midnightTRUNC(SYSDATE + 1)
Tomorrow at 8 AMTRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noonNEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnightTRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.mTRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)
»

Comments

Submitted by vedica (not verified) on Thu, 2005-12-15 06:14.

It was very helpful to solve my date/time problem

»

Submitted by Marie Griffiths (not verified) on Mon, 2007-01-15 06:35.

Don't forget there is:

add_month(sysdate, 1) -- adds a month to the current date

addmonth(sysdate, 12) -- adds a year taking into account leap years

»

Submitted by bhanuoracle on Thu, 2007-04-19 06:41.

These are very good queries.
though I do not have anything to add to these queries now, I could not restrain myself from appreciating those who have contributed to these queries.
these are very useful queries, used often in the program, yet we used to struggle for these queries.

Thanks a lot once again all those who have contributed to these questions.
keep up the good work.

»

Submitted by diplomaticc2 on Fri, 2012-12-28 08:50.

I've looked this up and can't figure out why this error keeps coming up. Can somebody give me an idea why this keeps coming up?

»