Re: 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:25:05 -0800 (PST)
Message-ID: <acdd2c41-90a3-4784-8545-4864517e6e33_at_l13g2000yqb.googlegroups.com>

On Dec 11, 11:05 am, yossarian <yossaria..._at_operamail.com> wrote:
> Jaap W. van Dijk wrote:
>
> > 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.
>
> case when regexp_like(date_string,format_regexp) then
> to_date(date_string,format_mask) else null

Is it possible to test with format_regexp if ('31112009', 'ddmmyyyy') is a valid date, or ('29022000', 'ddmmyyyy')?

Regards,
Jaap. Received on Fri Dec 11 2009 - 04:25:05 CST