| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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:GHofilena_at_cnv.org]
>
> 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?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Mar 26 2003 - 18:38:53 CST
 |
 |