Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: accept that is century-compliant
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