The DATA type stores date and time information. It stores the Year, Month, Day, Hours, Minutes and seconds. It has no problems with the Year2000 although some applications may use the format DD-MM-YY and assume a 1900 prefix when entering data.
The RR format specifier in TO_CHAR and TO_DATE functions allow to change to different centuries depending on the year. The years 50-99 will be 19xx and 00-49 will be 20xx. A global setting on the server can help migrating older applications. Note that this will also be true in the year 2000!
SQL> SELECT TO_CHAR( TO_DATE( '01/01/49', 'dd/mm/rr' )
,'dd-mm-yyyy' ) Before50
,TO_CHAR( TO_DATE( '01/01/99', 'dd/mm/rr' )
,'dd-mm-yyyy' ) After50
,TO_CHAR( SYSDATE, 'dd-mm-yyyy' ) Today
FROM DUAL;
BEFORE50 AFTER50 TODAY
---------- ---------- ----------
01-01-2049 01-01-1999 16-03-1998
-- Switch computer date to 2001
BEFORE AFTER TODAY
---------- ---------- ----------
01-01-2049 01-01-1999 08-04-2001
SQL>
Figure 1: How to use the RR format
If no time is defined in a date column, Oracle will take 12:00:00 am as a default. It is possible to calculate with date values. The value 1 will be one day, so 10 days or 1 hour from now may be derived from the database as:
SQL> SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI') "Now"
,SYSDATE + 10 "10 Days"
,TO_CHAR(SYSDATE + 1/24,'HH:MI ') "+1 hour"
FROM DUAL;
Now 10 Days +1 hour
---------------- --------- -------
14/04/1998 09:43 24-APR-98 10:43
SQL>
Figure 2: Date calculation
It is also possible to subtract two dates from each other. The resulting number will be the time that has elapsed between the two dates in days.
SQL> SELECT EName, HireDate
,TO_CHAR((SYSDATE - HireDate)/365,'99.9') "Years"
FROM EMP;
ENAME HIREDATE Years
---------- --------- -----
SMITH 17-DEC-80 17.3
ALLEN 20-FEB-81 17.2
WARD 22-FEB-81 17.2
JONES 02-APR-81 17.0
: : :
Figure 3: Years employed