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
RE: WinNT / 8.0.5 / DECODE function affecting Century result in dateI just did a test on the decode and
"DECODE('301230,NULL,NULL,TO_DATE('19'||'301230','YYYYMMDD'))")
returned 1930/12/30 as required while
"DECODE('301230,NULL,NULL,TO_DATE('301230','YYYYMMDD'))") returned 2030/12/30
Yechiel Adar
Mehish
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: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: Yechiel Adar INET: adar76_at_inter.net.il 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 Thu Mar 27 2003 - 03:54:02 CST