----- Original Message -----
Sent: Thursday, March 27, 2003 2:38
AM
Subject: RE: WinNT / 8.0.5 / DECODE
function affecting Century result in d
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?
