how to test a string for a valid date in sql
From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Fri, 11 Dec 2009 02:01:07 -0800 (PST)
Message-ID: <8f032f00-305d-472c-b478-a58db6082a19_at_e27g2000yqd.googlegroups.com>
I'm loading millions of records in a transformation step in a datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the transformation is converting a date-string in a VARCHAR2 column into a date in a DATE column, something like
Date: Fri, 11 Dec 2009 02:01:07 -0800 (PST)
Message-ID: <8f032f00-305d-472c-b478-a58db6082a19_at_e27g2000yqd.googlegroups.com>
I'm loading millions of records in a transformation step in a datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the transformation is converting a date-string in a VARCHAR2 column into a date in a DATE column, something like
CASE WHEN date-string is valid THEN TO_DATE(date-string) ELSE NULL END. Is it possible to test a string for a valid date in Oracle 9i/10g/11g without resorting to PL/SQL? If I use a homemade PL/SQL function, this is performed once for each record, which increases the load time considerably due to the millions of SQL - PL/SQL context switches back and forth.
Regards,
Jaap.
Received on Fri Dec 11 2009 - 04:01:07 CST