Re: 4-digit years

Reply emailed to author

Hi Dana,

    Two approaches to the problem.

1. Add check constraints to the tables so that dates prior to '01-JAN-99' (or whatever) aren't accepted. If you feel that this would result in an unacceptable level of trauma among your user community, i.e. they come after you with baseball bats...
2. Write some stored procedures that will add 1900 years to the dates that you want to fix. Set up the stored procedures so that they are run every night from dbms_jobs.

regards
Jerry Gitomer

Dana Reed wrote in message <37A06527.8FD954C8_at_oro.net>...
|We've changed our NLS_DATE_FORMAT to 'DD-MON-YYYY' from the
Oracle
|default of 'DD-MON-YY'. The problem is, we have lots of people
doing
|work on the system through SQL*Plus who are used to entering
2-digit
|years, only now a 2-digit year will implicitly be preceded by
'00'
|instead of '19'. Does anyone know how to get Oracle to reject
2-digit
|year values when the default year format is 4 digits? There is
an ISO
|year format 'IYYY' but it can only be used as a print format and
is
|invalid when setting NLS_DATE_FORMAT.
|
|We should have overriden the Oracle default 2-digit year when
system
|development was started in 1993 but we didn't know any better.
I would
|recommend anyone starting out with Oracle do this!
|
Received on Thu Jul 29 1999 - 11:52:55 CDT