Some precisions about ORACLE DATES.
Date: 20 Sep 1993 14:23:41 GMT
Message-ID: <27kedd$l3m_at_netserver.univ-lille1.fr>
Article 4241 of comp.databases.oracle:
Path: univ-lille1.fr!omega!noel
From: noel_at_omega.univ-lille1.fr (Yves Noel)
Newsgroups: comp.databases.oracle
Subject: Some informations about DATES on ORACLE V6
Date: 14 Sep 1993 13:17:08 GMT
Organization: CITI - Universite des Sciences et Technologie de Lille
Lines: 105
Sender: noel_at_omega (Yves Noel)
Distribution: world
Message-ID: <274g8k$1h4_at_netserver.univ-lille1.fr>
Reply-To: noel_at_omega.univ-lille1.fr (Yves Noel)
NNTP-Posting-Host: omega.univ-lille1.fr
Keywords: dates
X-Newsreader: mxrn 6.18-6
Hi all !
There are often discussions about DATES in this newsgroup. I think you'll be interested by some "little things" it'd be important to know about the utilisation of DATES through ORACLE V6.
With ORACLE V6, the kernel takes (by default), the century corresponding to the pseudo-column SYSDATE. Then, unless an explicit mask containing the YYYY format has been specified, all dates will be entered and posted like 19xx until 2000. This explain that the use of the TO_DATE function on a DATE datatype column return 19xx :
select to_char(to_date('14-SEP-93'),'DD-MON-YYYY') day_j from dual;
DAY_J ---------------------- 14-SEP-1993
In return, if a YYYY mask has been specified, it becomes obligatory to give information on the century, otherwise it'll be forced with zero :
select to_char(to_date('14-SEP-93','DD-MON-YYYY'), 'DD-MON-YYYY') ...
DAY_J --------------------- 14-SEP-0093 We suppose now a table with a column that contains the two dates under : 14-SEP-1993 14-JUL-1789 (French revolution !!!)
then, IT'S IMPORTANT TO UNDERSTAND that the next request "force" the year 1789 to 1989 :
select to_char(to_date(hiredate), 'DD-MON-YYYY') hiredate from emp;
HIREDATE ---------------------- 14-SEP-1993 14-JUL-1989
Indeed, the TO_DATE function transform a character string in a date; if the
century is not precised in the string then it is deduct assuming is courant
value (todays date). In return, if the argument passed to the TO_DATE function
isn't a character string but a date then this one is FIRST CONVERTED in a
character string with the default format, i.e. DD-MON-YY, then on eliminating
the reference on the century.
In our case, the date 14-JUL-1789 is transformed in string 14-JUL-89. This
string is converted in date, what force the passage on the 20th century.
TO AVOID THIS CENTURY CONVERSION, IT'S NECESSARY TO AVOID THE UTILISATION OF THE TO_DATE FUNCTION : select to_char(hiredate,'DD-MON-YYYY') hiredate from emp;
HIREDATE -------------------------- 14-SEP-1993 14-JUL-1789
THROUGH SQL*FORMS ...
The situation is more complicated when we don't directly use stocked dates in the database, but when the data acquisition is made by an 'applicatif' which use formated fields. We suppose an SQL*Forms 3.0 application with date fields on a DD-MON-YYYY format and a carefree user who enter the value 14-SEP-18.
What would be the attitude of SQL*Forms ?
- Anterior versions of SQL*Forms (generally up to 3.0.15) suppose the user was a little thoughtless and force the century to the current value. In our exemple 14-SEP-1918 was inserted in the database (but 14-SEP-0018 was posted again on the screen).
- In fact, our user can be not thoughtless but an historian and he really want to insert dates like 25-DEC-0002 or 22-MAR-0732 on data acquisition by choosing 25-DEC-2 or 25-DEC-02 or 25-DEC-0002. The recently versions of SQL*Forms don't make default conversion on the current century.
- It'll be possibly necessary to place a trigger ON-VALIDATE-FIELD to force the century on fields of type DD-MON-YYYY and which aren't assigned to receive "fantaisists" dates.
P.S. The version 7 of ORACLE is allowed to eliminate some of these precautions
by two ways :
- on using the parameter NLS_DATE_FORMAT in INIT.ORA file which specify the default format than dates have to take;
- on using the mask with format RR instead of YY to take the next century if the given year is < 50.
P.S. 2 Excuse my poor English !!!
--Received on Mon Sep 20 1993 - 16:23:41 CEST
------------------------------------------------------------------------------
_/_/_/_/ _/_/_/_/ _/_/_/_/ _/_/_/_/ _/_/ _/_/ _/_/ _/_/ _/_/ _/_/ _/_/ _/_/ _/_/_/_/ _/_/_/_/ _/_/ _/_/_/_/
------------------------------------------------------------------------------
Yves NOEL - Database Administrator C.I.T.I. (batiment M4) Phone : (33) 20.43.42.70 Universite des Sciences & Technologies de Lille Fax : (33) 20.43.66.25 59655 Villeneuve d'Ascq Cedex - FRANCE Email : noel_at_univ-lille1.fr
------------------------------------------------------------------------------
I use ORACLE v6.0.36.5.2 on DEC RISC ULTRIX v4.3
------------------------------------------------------------------------------