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: Force four-digit year?

Re: Force four-digit year?

From: Arien Malec <amalec_at_slip.net>
Date: Fri, 27 Aug 1999 10:49:02 -0700
Message-ID: <37C6CF8E.D0569F40@slip.net>


What you want is the fx modifier:

nls_date_format = 'MM/DD/fxYYYY'

select to_date('01/01/99') from dual;
TO_DATE('



ORA-01862: the numeric value does not match the length of the format item

fx acts like a switch that turns on and off exact matching. So, for instance, 'fxMM/fxDD/fxYYYY' willl accept '01/1/1999' but 'fxMM/DD/YYYY' won't. The format mask 'MM/DD/fxYYYY' will be loose for MM and DD, but exact for YYYY.

Arien

Yosi Greenfield wrote:

> Anyone know of a way to force the user to enter four-digit year for
> implicit conversions? Setting NLS_DATE_FORMAT will cause the database to
> allow four digits, and depending if you use RRRR oe YYYY will set it to
> something or other.
>
> But you can still enter a two-digit year using implicit conversions
> (without to_date). Oracle will then set the century to 00 (for YY) or 19
> or 20 (for RR).
>
> Support said you can't force a four digit year. They kindly suggested I
> write triggers on all my date fields. Thank you Support.
>
> Any better ideas? For lack of anything else, I'll end up with RRRR and
> some stern memos to our developers. But I'd rather force the developers
> to do something they'd rather not do - you know, code properly.
>
> Thanks,
>
> Yosi
Received on Fri Aug 27 1999 - 12:49:02 CDT

Original text of this message

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