RE: WinNT / 8.0.5 / DECODE function affecting Century result in d

From: George Hofilena <GHofilena_at_cnv.org>
Date: Thu, 13 Sep 2001 10:23:27 -0700
Message-ID: <F001.0038D811.20010913095021@fatcity.com>

!! Please do not post Off Topic to this List !!

Thanks for all the responses. I just switched the operators like this. Seems to work.

TO_DATE(DECODE(:bdate,NULL,NULL,'19'||:bdate),'YYYYMMDD')

This is legacy data so the '19' prefix is only temporary. RR was one of the first that I tried. Didn't work.
Anyway, I guess I was just curious as to why Oracle changed the century in such a manner when I introduce the DECODE the way I originally did.

This is no longer urgent in my case but I'd still be interested to know if somebody has any input as to why Oracle behaves the way it does.

Thanks,

George

-----Original Message-----
Sent: Thursday, September 13, 2001 10:00 AM To: Multiple recipients of list ORACLE-L d

!! Please do not post Off Topic to this List !!

What about RRMMDD as the date format? Will that work?

Steven Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
MonaghaS_at_mscdirect.com
http://www.mscdirect.com

-----Original Message-----
Sent: Thursday, September 13, 2001 5:45 AM To: Multiple recipients of list ORACLE-L date

!! Please do not post Off Topic to this List !!Hi George

Your code looks fine to me and I have no idea why it wouldn't work. But if it turns into a pain in the neck, why not write a wee PL/SQL function to do the century thing for you and also trap nulls ?

There's a reason you might want to do this anyway: presumably you will sooner or later start receiving dates in the current century, e.g. if your file contains 000101 don't you want a function that will turn it into 01-JAN-2000 rather than 01-JAN-1900 ?

Cheers
Greg

-----Original Message-----
Sent: Wednesday, 12 September 2001 01:12 To: Multiple recipients of list ORACLE-L date

List,

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:

SQL Operation: (bdate POSITION(001:006) CHAR "TO_DATE(:bdate,'YYMMDD')") Result in the database: Next Century, e.g. '300223' becomes '23-FEB-2030'
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?

Thanks,

George

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: George Hofilena
INET: GHofilena_at_cnv.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Greg Solomon
INET: greg.solomon_at_betfair.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you are not the intended recipient, please do not read, copy, use or disclose the contents of this communication to others. Please notify the sender that you have received this e-mail in error by replying to the e-mail. Please then delete the e-mail and destroy any copies of it. Thank you.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Steven Monaghan
INET: MonaghaS_at_mscdirect.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------