Oracle SQL date values

Found some really useful information on SQL date values at the excellent SQL FAQ. I've added a couple of 'real world' examples at the bottom that I used in some recent development work:

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:

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 the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 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)

Real world examples:

(i) This first one sets the date to be the 1st of the month (by using the last_day function to get the end of the current month, then add 1 day) and the time to be 7am (7/24):

update mc_jobs set scheduledtime=TRUNC(LAST_DAY(SYSDATE ) + 1) + 7/24 where jobid=3596

(ii) And this one uses next_day to set the date to be the next Monday after today, at 6am (6/24):

update mc_jobs set scheduledtime=NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 6/24 where jobid=3536