Re: F3 Two digit fields & Century Change

From: Yves Noel <noel_at_omega.univ-lille1.fr>
Date: 27 Oct 1994 09:02:53 GMT
Message-ID: <38nqbt$haq_at_netserver.univ-lille1.fr>

In article <38m9dj$n5g_at_eccdb1.pms.ford.com>, jordan_at_pt5026.pto.ford.com (Dave Jordan) writes:
|>Forms 3.0.16
|>RDBMS 7.1.3.2.1
|>OS Dec Alpha OSF/1 V3.0
|>
|>Does anyone know how to keep the default date format (dd-mon-yy) for a field,
|>but handle the cenutury change?
|>Oracle gives us 'RR' to do the conversion, but I can't figure out where
|>to use it to make this thing work.
|>The 'RR' part of the format will convert 00-49 to the next century and leave
|>50-99 in the current.
|>
|>Things we have tried that have failed:
|>
|>1) Change NLS_DATE_FORMAT to DD-MON-RR in the init.ora file
|> This doesn't work, and keeps sqlmenu50 designer from working.
|>
|>2) Put DD-MON-RR in the format mask for the date field.
|> This gives an error: "FRM-40209 Field must be of form DD-MON-RR"
|> Duhh... I entered 12-MAY-09! What are they looking for?
|>
|>3) Create a database trigger to modify the 'NEW:' value with the 'RR'
|> conversion.
|> Upon commiting the row, forms complains that another user has modified
|> your row and you should requery.
|>
|>We are not about to go through the kludge of creating non-db fields for all
|>the applications.
|>
|>Dave Jordan
|>
Put a trigger ON-VALIDATE-FIELD to force the century, something like :

PROCEDURE fix_date (f_date IN OUT DATE) IS

	l_siecle char(2) := substr(to_char(sysdate,'YYYY'),1,2);
	l_annee number(4) := to_number(to_char(f_date,'YYYY'));
	l_tempo  char(11);
BEGIN
	if (l_annee < 100) then
	  l_tempo := to_char(f_date,'DD-MON-')||l_siecle||lpad(to_char(l_annee),
2,'0');
	  f_date := to_date(l_tempo,'DD-MON-YYYY');
	end if;

END; This exemple was in 'Bulletin Technique ORACLE number 8 summer 1992'.

-- 
 __  __ _  __ ____ ____    _   _  ____  ____ _
(  )(  ) )(  )  __)  __)  | \ | |/ __ \|  __) |  Administrateur Bases de Donnees
 \ \/ /\ \/ /| (_  \ \    |  \| | /  \ \ (_ | |   Mail: Yves.Noel_at_univ-lille1.fr
  \  /  \  / | (__ _\ \   | |\  | \__/ / (__| |_          Tel : +33  20.33.70.74
  (__)   \/  |____)____)  |_| \_|\____/|____)____)        Fax : +33  20.43.66.25
  Universite des Sciences & Technologies de Lille  59655 Villeneuve d'Ascq Cedex
                                   

Received on Thu Oct 27 1994 - 10:02:53 CET