How can I interpret a two digit year after 2000 ?

When converting to dates from characters when you only have two characters for the year, the picture format "RR" will be interpreted as the year based on a guess that that date is between 1950 and 2049.

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

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-2002 16:47:23 03-JUL-2002 16:47:53

Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of next month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
First day of the current month	TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) + 1
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY'' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

How does one get the time difference between two date columns?

select floor(((date1-date2)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600 -

(floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))

|| ' SECS ' time_difference

from ...

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

select to_char(to_date('00:00:00','HH24:MI:SS') +

(date1 - date2), 'HH24:MI:SS') time_difference

from ...

Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.