Re: 2000 AD - Oracle Date Format
Date: 1996/03/23
Message-ID: <4ivnks$k8q_at_inet-nntp-gw-1.us.oracle.com>#1/1
Erik Lindquist <elindquist_at_logicon.com> wrote:
>Just wondering... Currently the default date format for Oracle is
>SELECT...
>DD-MON-YY. What will happen in the year 2000 if I query the database
>like this:
>FROM ...
>WHERE DATE_COLUMN = '22-MAR-96'
>Is this date going to evaluate to the year 1996 or 2096?
SQLDBA> select sysdate from dual;
SYSDATE
01-JAN-00
1 row selected.
SQLDBA> select to_char(sysdate,'dd-mon-yyyy') from dual; TO_CHAR(SYSDATE,'DD-MON-YYYY')
01-jan-2000 1 row selected.
SQLDBA> select to_char( to_date( '22-MAR-96' ), 'dd-mon-yyyy' )
2> from dual;
TO_CHAR(TO_DATE('22-MAR-96'),'DD-MON-YYYY')
---------------------------------------------------------------
22-mar-2096
1 row selected.
SQLDBA>
>Anyone know? Thanks.
I didn't, until I reset my clock and tried it. According to the doc, the YY format will return a date in the current century. There is an ongoing argument as to when the next century starts. Some say 2000, others 2001. Apparently Oracle thinks the year 2000 is in the next (21'st) century.
BTW, The RR date format may be applicable to many people as we approach the turn of the century. Lets say it is late in the month of december, 1999 and you have an order entry system. Your terminal accepts 02-JAN-00 as the input date for an order to be shipped. Unfortunately, your customer will never get their order since the 00 in the year 1999 will be interpreted as 1900, not 2000. The RR date format, as opposed to YY, will interpret this date as Jan 2, 2000. You can reset the default date format at the database level if need be using the init.ora parameter nls_date_format or at the application level by issueing an alter system set nls_date_format command. The following shows how this will affect dates entered in the next century today:
SQLDBA> select to_char(sysdate,'dd-mon-yyyy') from dual; TO_CHAR(SYSDATE,'DD-MON-YYYY')
22-mar-1996 1 row selected.
SQLDBA> select to_char( to_date('02-JAN-00'), 'dd-mon-yyyy') from dual; TO_CHAR(TO_DATE('02-JAN-00'),'DD-MON-YYYY')
02-jan-1900 1 row selected.
SQLDBA> alter session set nls_date_format='DD-MON-RR'; Statement processed.
SQLDBA> select to_char( to_date('02-JAN-00'), 'dd-mon-yyyy') from dual;
TO_CHAR(TO_DATE('02-JAN-00'),'DD-MON-YYYY')
02-jan-2000 1 row selected.
SQLDBA>
>-Erik. elindquist_at_logicon.com
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Sat Mar 23 1996 - 00:00:00 CET