Date/time functions for date manipulation

If you use a two-digit year as input, Oracle assigns the current century to the date. This became a problem in the year 1999, as programmers prepared their systems for the year 2000. As a result, Oracle created a new date format:

The RR format is a rounding function for two-digit years that allows you to input a two-digit year and have it adjusted to the correct century, for most applications.
So long as the last two digits of the current year are between 00 and 49, the RR format assigns the current century to all two-digit dates between 00 and 49 that are inserted or updated into the database. For incoming dates between 50 and 99, the previous century is assigned. When the last two digits of the current year are between 50 and 99, the RR format assigns the next century to all incoming dates with two-digit years between 00 and 49. For incoming dates between 50 and 99, the current century gets assigned.
Of course, you can always input a four-digit year and have it loaded exactly as you type it into the database.

DATE is an Oracle datatype, just as VARCHAR2 and NUMBER are, and it has its own unique properties. The DATE datatype is stored in a special internal Oracle format that includes not just the

1. month,
2. day, and
3. year,

but also the hour, minute, and second. The benefit of all this detail should be obvious. If you have, for instance, a customer help desk, for each call that is logged in, Oracle can automatically store the date and time of the call in a single DATE column. You can format the DATE column on a report to show just the date, or the date and the hour, or the century, date, hour, and minute, or the date, hour, minute, and second. You can use the TIMESTAMP datatype to store fractional seconds.

SQL*Plus and SQL recognize columns that are of the DATE datatype, and they understand that instructions to do arithmetic with them call for date arithmetic, not regular math. Adding 1 to a date, for instance, will give you another date, which is the next day. Subtracting one date from another will give you a number which is the count of days between the two dates. However, because Oracle dates can include

1. hours,
2. minutes, and
3. seconds,

doing date arithmetic can prove to be challenging because Oracle could tell you that the difference between today and tomorrow is .516 days.

Oracle taps into the operating system of the computer for the current date and time. It makes these available to you through a special function called SYSDATE. Think of SYSDATE as a function whose result is always the current date and time, and it can be used wherever any other Oracle function can be used. You also can regard it as a hidden column or pseudo-column that is in every table. Here, SYSDATE shows the date of today:

select SysDate from DUAL;
SYSDATE
---------
28-FEB-08

Note: DUAL is a small but useful Oracle table created for testing functions or doing quick calculations.