Date Math in Oracle

If you are using Oracle 9i or greater, you can use the interval data type to do simple Date arithmetic as follows:

select sysdate + interval '2' hour from dual; -- to add 2 hours to current time

select sysdate + interval '5' minute from dual; -- to add 5 minutes to current time

select sysdate + interval '24' second from dual; -- to add 24 seconds to current time

For adding or subtracting days, you can simply add it as below:

select sysdate + 3 from dual; -- to add 3 days to current date

select sysdate - 6 from dual; -- to subtract 6 days to current date

select last_day(sysdate) - sysdate from dual; -- to find the number of days from current date to end of month

However when adding months, it is better to use the functions provided by Oracle:

select add_months(sysdate, 2) from dual; -- to add 2 months to current date

select add_months(sysdate, -3) from dual; -- to subtract 3 months to current date

To select the date of the next specified day, you can use the NEXT_DAY function:

select next_day(sysdate, 'SAT') from dual; -- to get the date on next Saturday after current date
-- use SUN, MON, TUE, WED, THU, FRI or SAT

To get the last date of the month for the specified day, you can use the LAST_DAY function:

select last_day(sysdate) from dual; -- to get the date on last day of current month