!! 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
[EMAIL PROTECTED]
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:
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?
Thanks,
George
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Hofilena
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: George Hofilena
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
