Re: Y2k solution?

Hi -

What I have done on our UNIX database server is in the initXXXX.ora file add the following line and then stop and start the database. This file is normally located in $ORACLE_HOME/dbs.

You also need to do the same on your PC clients in the registry, either regedit or regedt32 and then
hkey_local_machine, software, oracle. When you use Oracle Developer forms, I believe this takes precedence over the database server nls_date_format.

        nls_date_format = dd-MOON-rr

you can also use

        nls_date_format = dd-MON-rrrr

What this will do for you is it will convert the dates properly between years (19)50 and (20)49.

But, if you really want to enter (19)02 it will go into the database as 2002 and NOT 1902.

You can play with these scenarios by setting the nls_date_format in your SQL*Plus session:

        alter session set nls_date_format = 'dd-MON-rr';

In my opinion, you need to force the users to use 4 digit year to take care of this problem at the application level be it Forms, SQL*Loader or SQL*Plus.

This is the way we have taken care of this measure in forms:

          Stop the user from entering a 2 digit year in the form.

        Set the date format mask to dd-MON-yyyy in the form, and trap the error via the following procedure in the ON-VALIDATE-FIELD trigger for the date item in the form if they try to use a 2 digit year.

      	      procedure check_date_format(pl_date in date)
		 	is
      	      BEGIN
      	      /*  **************  Reference from LIBRARY LIB_GNRL 
*************  */
      	      /*
         	      Format mask DD-MON-YYYY on date field does not work
properly.
         	      For example, date entered as 15-OCT-93 changes to
15-OCT-0093
         	      rather than 15-OCT-1993.  This error is being trapped by
this
         	      procedure check_date_format and ON-VALIDATE-FIELD trigger
or
         	      KEY-NXTFLD trigger for the date field(s).

            	 	Input parameter     pl_date= date to be checked
      	      */
      	   		if substr(to_char(pl_date,'DD-MON-YYYY'),8,2) = '00' then
         	      		message('Date must be entered in DD-MON-YYYY format,
Example
                        		01-JAN-1993.');
         	      		raise form_trigger_failure;
      	   		end if;
      	   	END;

		In your ON-VALIDATE-FIELD trigger on the date item you simply need to

                               check_date_format(:block1.date1);

The procedure can be modified to accommodate appropriate date format mask used in the form such as mm/dd/yyyy etc.

If you need examples of doing this in SQL*Loader or SQL*Plus please let me know.

Later !!!

Suresh Bhat
Oracleguru
www.oracleguru.net

[Quoted] Muhammad Ismail Thahim <ismail_at_super.net.pk> wrote in article <3779D995.691057BC_at_super.net.pk>...
> can some one help me
> what is the Y2k solution for date
> i.e
> 1- if the user enters 99 system will turn 99 to 1999
> 2- if user enters 00 then the system will turn 2000
> 3- if user enters 59 system will turn 1959
> is there any command , procedure that can be applied on when validate
> trigger or any other trigger to do all these things.
>
> ismail
> ismail_at_super.net.pk
>
>
Received on Thu Jul 01 1999 - 17:17:34 CEST