Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: accept that is century-compliant

Re: accept that is century-compliant

From: Suresh Bhat <oracleguru_at_mailcity.com>
Date: Fri, 19 Mar 1999 18:12:36 GMT
Message-ID: <01be7243$1ba7a720$a504fa80@mndnet>


Hi -

As some one else suggested, setting initXXXX.ora parameter NLS_DATE_FORMAT to 'dd-MON-yyyy' does not guarantee that the 4 dgit year data entry will be enforced in your SQL*Plus session or any other Oracle product such as Oracle forms. You can still enter a two digit year and it will take it and append 00 in front of it. For example, 01-MAR-99 will look like 01-MAR-0099. Also you should not use single quotes as some one else suggested. If you are going to set the NLS_DATE_FORMAT in your initXXXX.ora file, use double quates or no quotes at all. I went through this myself. It appends quotes on both sides of the date when you select a date data type. Single quotes only work in SQL*Plus session.

NLS_DATE_FORMAT = "dd-MON-yyyy" or dd-MON-yyyy

Now, if you want to stop your SQL*Plus session from executing if some one enters two digit year, here is the way do it:

set doc off feedback off verify off pause off  

whenever sqlerror exit /* This is the key */  

/*
Setting date format in session to 4 digit year. */
alter session set nls_date_format = 'dd-MON-yyyy' /

accept dt prompt "Enter date (dd-mon-yyyy): "  

select to_char(to_date('&dt','dd-mon-yyyy'),'dd-mon-yyyy') entered_date   from dual
/
set heading off  

select 'Two digit year entered, Please re-submit with 4 digit year. Exiting program.'
  from dual
 where substr(to_char(to_date('&dt','dd-mon-yyyy'),'dd-mon-yyyy'),8,2)='00' /
prompt
prompt
/*
Get out if two digit year entered.
*/
set termout off  

select 1/0 /* This is the key to exiting */   from dual
 where substr(to_char(to_date('&dt','dd-mon-yyyy'),'dd-mon-yyyy'),8,2)='00' /
set termout on heading on feedback on /* etc. */  

select sysdate from dual
/
prompt
prompt
exit

Good luck !!!

Suresh
www.oracleguru.net
oracleguru_at_mailcity.com

hf <hessel_at_fdp.co.za> wrote in article
<7cqn0d$18jv$1_at_nnrp01.iafrica.com>...
> How do I make the following century compliant?
> My email is: hessel_at_fdp.co.za
>
>
> accept dt prompt "Enter date (dd-mon-yyyy): "
> select count(*) from file1 where tr_date >= '&dt'
>
>
>
>
>
Received on Fri Mar 19 1999 - 12:12:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US