Re: Y2K Date Issues with Oracle 7.3.4

From: Peter L <news_at_abc.freeserve.co.uk>
Date: Sun, 16 Jan 2000 13:00:17 -0000
Message-ID: <85shtn$rva$1_at_news5.svr.pol.co.uk>


Sofia wrote in message <85qgh1$2nf2$1_at_ulysses.noc.ntua.gr>...
>interesting fact:
>
>If you cast "to_date" a date variable (don't ask me why you would
>like to do that), then no matter how much care you took in placing
>the correct date in the variable, the result will be a date after 2000.
>
>For example
>
>a_date DATE;
>
>a_date := to_date('12/10/1998','DD/MM/YYYY');
>a_date := to_date(a_date);
>
>now a_date has a 12/10/2098 value!
>
This behaviour is fairly easy to explain. The to_date function expects a character input. When you to_date something that is already a date Oracle will do an implicit to_char to your date to allow the to_date. This to_char is done to the default date format, typically something like '12-Oct-98'. You now to_date this char string, from this standard date format, which doesn't have a century, so the current century '20' is assumed. If you were to do the to_date with YYYY you would end up with '0098'.

If you need to do a to_date to, for example, guarantee the correct century according to the 'RR' rules, when you aren't too confident how the date has been handled you can do something like
to_date(to_char(:my_date),'dd-Mon-rr'). Received on Sun Jan 16 2000 - 14:00:17 CET

Original text of this message