Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Force four-digit year?
What you want is the fx modifier:
nls_date_format = 'MM/DD/fxYYYY'
select to_date('01/01/99') from dual;
TO_DATE('
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