Re: 4-digit years

We don't want to go to all the trouble of additional constraints or triggers. Dave Thompson kindly suggested using 'RRRR' instead of 'YYYY' and it works perfectly. 2-digit years are still accepted but are implicitly prefixed by '19' instead of '00'.

Jerry Gitomer wrote:

> 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 - 12:17:48 CDT