I don't understand the question. It seems to me that the date conversion is acting as expected.
to_date ('301231', 'YYMMDD') should be 31 December 2030. If you don't specify a century, the century defaults to the current century.
on the other hand,m
to_date ('19' || '301231', 'YYYYMMDD') will be converting '19301231' which will be 31 December 1930.
It seems to me that you are trying to say that "19" is the "correct" century. How is the software supposed to know that?
The root cause of the problem is using 6-year dates. I thought all of those went away during the great IT boom project of Y2K conversion. (remember all those planes that were going to fall out of the sky?)
If you want to change two-digit years in a certain range to the 19th century, you will have to do something like this:
(let's pretend every two-digit year >= 10 is the 19th century, but years from 0 to 9 are in the 20th century)
decode (:bdate, null, null, decode (sign (to_number (substr (:bdate, 1, 2)) - 10), -1, to_date (:bdate, 'YYMMDD'), to_date ('19' || : bdate, 'YYYYMMDD')))
Note: the RR date format for two-digit years won't do the trick because RR does its thing only if the last two digits of the current year are >= 50 or the year you're converting is >= 50, and neither are true in this person's example.
From the 8.0 documentation:
RR: Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.
> -----Original Message-----
> From: George Hofilena [mailto:[EMAIL PROTECTED]]
>
> I have a 6-position column, bdate, in a text file that I am trying to
> SQL*Load formatted as yymmdd. When I use the following SQL
> operations I get
> the following results:
>
> 1. SQL Operation: (bdate POSITION(001:006) CHAR
> "TO_DATE(:bdate,'YYMMDD')")
> Result in the database: Next Century, e.g. '300223' becomes
> '23-FEB-2030'
>
> 2. SQL Operation (bdate POSITION(001:006) CHAR
> "DECODE(:bdate,NULL,NULL,TO_DATE('19'||:bdate,'YYYYMMDD'))")
> Result in the database: Next Century, e.g. '300223' becomes
> '23-FEB-2030'
>
> but when I take out the DECODE in item 2, I get the correct
> century. I only
> use decode because there are null values in this column. Can somebody
> explain to me what I am missing here?
