Y2K Date Issues with Oracle 7.3.4

[Todd Riley]

I am having problems with the date format in Oracle 7.3.4.
There are some sections of our code where, when using the
to_date function, we have used the two-digit year (e.g.
to_date('01/11/99','mm/dd/yy') ). I tested this in SQL
Worksheet using the code below:

select to_char( to_date( '01/06/99' , 'mm/dd/yy' ),
'mm/dd/yyyy' ), to_char ( to_date( '01/06/1999' ,
'mm/dd/yyyy' ), 'mm/dd/yyyy' )
from dual;

The result:

TO_CHAR(TO_DATE('01/06/99','MM/DD/YY'),'MM/DD/YYYY')
-----------------------------------------------------
01/06/2099

TO_CHAR(TO_DATE('01/06/99','MM/DD/YYYY'),'MM/DD/YYYY')
-----------------------------------------------------
01/06/1999

Well, the obvious fix is to change to the four-digit date
format, but that is not the extent of my problem. I know
the reason this is happening is because according to the
Oracle book ÿffffc3ÿffffa2ÿffffe2ÿffff82ÿffffacÿffffc5ÿffff9350-99 use the current century (so 99 becomes
1999) while year values of 00-49 are rounded to the next
century (so 01 becomes 2001). This was obviously written
in 1999 - so now that the current century is 2000, 99
becomes 2099. But the big problem that I was having is
when dates are passed as parameters. For some reason,
Oracle is still only accepting the two-digit year when
passed as a parameter and then the above error applies.
What we have had to do so far is to change the date to a
string using to_date, pass it as parameter, do what ever
changes to that variable, and change it back to a date
value. There has to be an easier way. Any suggestions?

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful

[James Lorenzen]

The Oracle date format ("yy") does not "window". If you use it in the
TO_DATE function, the century portion is defaulted to the current
century. Your example below demonstrates this. If you want Oracle to
"adjust" the century for you, you need to use "rr" instead of "yy". The
"rr" will window the date, adjusting the century.

I.E.

TO_CHAR(TO_DATE('01/06/99','MM/DD/YY'),'MM/DD/YYYY')
-----------------------------------------------------
01/06/2099

TO_CHAR(TO_DATE('01/06/99','MM/DD/RR'),'MM/DD/YYYY')
-----------------------------------------------------
01/06/1999

HTH
James

In article <26f4216e...@usw-ex0110-075.remarq.com>,

Sent via Deja.com http://www.deja.com/
Before you buy.

[Mungo Henning]

James Lorenzen wrote:

> [good stuf snipped]

Just a little warning: remember that the RR format is fine for "recent"
dates,
but if you have anyone born before 1950 (data for insurance policies etc)
then the RR format will give you grief when it interprets "49" as "2049".

Mungo

--
Mungo Henning - it's a daft name but it goes with the face...
mungoh@itacs.strath.ac.uk.http://www.itacs.strath.ac.uk/
I speak for me, not my employer.

[Steve McDaniels]

We simply use 'YYYY' in all date conversions

to_date('01-JAN-1999','DD-MON-YYYY')
and
to_char(sysdate,'DD-MON-YYYY')

Mungo Henning <mun...@itacs.strath.ac.uk> wrote in message
news:387DE3D7...@itacs.strath.ac.uk...

[Sofia]

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!

Steve McDaniels <steve.m...@sierra.com> wrote in message
news:85lnet$lki$1...@plo.sierra.com...

[pber...@my-deja.com]

Sofia,
And if you want to see something even more bizarre, look at what
happened when I tried to prevent the century shift by specifying a
format mask when TO_DATEing the date:

SQL> DECLARE
2 a_date DATE;
3 BEGIN
4 a_date := to_date('12/10/1998','DD/MM/YYYY');
5 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
6 a_date := to_date(a_date);
7 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
8 a_date := to_date('12/10/1998','DD/MM/YYYY');
9 a_date := to_date(a_date,'DD/MM/YYYY');
10 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
11 END;
12 /
12/10/1998
12/10/2098
12/10/0098

PL/SQL procedure successfully completed.

Now where in heck did that 0098 come from? Proving once again you have
to be VERY careful what you ask for when it comes to date I/O in Oracle.
Hope this gives you a chuckle,
Paul

In article <85qgh1$2nf2$1...@ulysses.noc.ntua.gr>,

[Peter L]

Sofia wrote in message <85qgh1$2nf2$1...@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').

[fumi]

Sofia <so...@eexi.gr> wrote in message news:85qgh1$2nf2$1...@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!

No.
It because that the datatype of the 1st parameter of TO_DATE() function
is CHAR/VARCHAR2!
If you put a DATE in the 1st parameter, Oracle will implicitly convert
to a string using TO_CHAR() function.

[Pierre Charpenay]

pber...@my-deja.com a écrit :

> Sofia,
> And if you want to see something even more bizarre, look at what
> happened when I tried to prevent the century shift by specifying a
> format mask when TO_DATEing the date:
>
> SQL> DECLARE
> 2 a_date DATE;
> 3 BEGIN
> 4 a_date := to_date('12/10/1998','DD/MM/YYYY');
> 5 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
> 6 a_date := to_date(a_date);
> 7 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
> 8 a_date := to_date('12/10/1998','DD/MM/YYYY');
> 9 a_date := to_date(a_date,'DD/MM/YYYY');
> 10 DBMS_OUTPUT.PUT_LINE(to_char(a_date,'DD/MM/YYYY'));
> 11 END;
> 12 /
> 12/10/1998
> 12/10/2098
> 12/10/0098
>
> PL/SQL procedure successfully completed.
>
> Now where in heck did that 0098 come from? Proving once again you have
> to be VERY careful what you ask for when it comes to date I/O in Oracle.
> Hope this gives you a chuckle,
> Paul
>

I presume your default format is 'DD/MM/YY', so
- lines 4 and 5 : OK
- lines 6 and 7 : automatic conversion of the date into char using the
default format
gives '12/10/98' (using the current century for the
output)
- lines 9 and 10 : first automatic conversion like above, and conversion to
a date using the
given format, so the '98' corresponds to 'YYYY' !

Funny !

Pierre

[Alexander I. Doroshko]

Nothing bizarre. When You call TO_DATE with the first argument of DATE type,
implicit conversion to CHAR type occurs, using Your NLS_DATE_FORMAT.
If NLS_DATE_FORMAT='DD/MM/YY', the expression in the line 9 is equivalent
to
to_date('12/10/98', 'DD/MM/YYYY'), which gives exactly 0098, because too
short
elements are left-padded with '0'.
And the line 6 is equivalent to
to_date('12/10/98', 'dd/mm/yy'), which gives 2098.
So, everything is in strict accordance with the docs.
But IMHO, experiments with using to_date with DATE argument are of purely
academic interest.
<pber...@my-deja.com> wrote in message news:85qpe9$9lk$1...@nnrp1.deja.com...

[pber...@my-deja.com]

Alexander,
On one level I would agree with you, on another it can be argued
that real world programmers are often concerned only with obtaining a
result that is correct today. That is to say, if they get the correct
result, they do not always ask if that result came from correct logic
or from incorrect logic that only worked because of some outside
circumstance. The code Sofia submitted would have worked perfectly in
many cases prior to 1/1/2000. So would the code in Todd's original
question. The lethal 'fix' for Sofia's error that I submitted would
actually give correct answers in a few cases. This obviously does not
mean the logic of any of these was correct; merely that the errors,
especially the original one, were less likely to be noticed and
corrected. Therefore, IMHO, the WHY may be of only academic interest,
but if specific errors in output begin to show up, knowing at least one
of the coding errors that can cause them is beneficial and knowing what
NOT to do to fix them can save time an aggravation.

The real message here is that in Oracle systems you can never go
wrong if you always pass a date as AND accept a date from, a character
string with a 4 digit year and use the same character string
consistently:
param1 := TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS');
new_date := TO_DATE(param1,'MMDDYYYYHH24MISS');
Any method that relies on factors outside the programmers direct and
continual control, such as NLS_DATE_FORMAT, is subject to failure.
I'll get down off my soapbox now.
Have a nice day,
Paul

[Pascal Glauser]

Obviously, to_date implicitly converts it's argument to a character,
using to_char() and the nls_date_format of the current session. If you
change the latter to 'DD.MM.RR', dates in the range from 1.1.1950 to
31.12.2049 are treated correctly:

select * from v$nls_parameters
where parameter = 'NLS_DATE_FORMAT'
yields:
NLS_DATE_FORMAT DD.MM.RR

The dbms-Output of the block
declare
wDate date ;
begin
wDate := to_date('31.12.1949','DD.MM.YYYY') ;
dbms_output.put_line('Before: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date(wDate) ;
dbms_output.put_line('After: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date('1.1.1950','DD.MM.YYYY') ;
dbms_output.put_line('Before: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date(wDate) ;
dbms_output.put_line('After: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date('31.12.2049','DD.MM.YYYY') ;
dbms_output.put_line('Before: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date(wDate) ;
dbms_output.put_line('After: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date('1.1.2050','DD.MM.YYYY') ;
dbms_output.put_line('Before: ' || to_char(wDate,'DD.MM.YYYY')) ;
wDate := to_date(wDate) ;
dbms_output.put_line('After: ' || to_char(wDate,'DD.MM.YYYY')) ;
end ;

is as follows:
Before: 31.12.1949
After: 31.12.2049
Before: 01.01.1950
After: 01.01.1950
Before: 31.12.2049
After: 31.12.2049
Before: 01.01.2050
After: 01.01.1950

In article <85qgh1$2nf2$1...@ulysses.noc.ntua.gr>,
"Sofia" <so...@eexi.gr> wrote:

> 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!
>