Re: how to test a string for a valid date in sql
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 13 Dec 2009 10:58:52 +0100
Message-ID: <4b24bae0$0$22943$e4fe514c_at_news.xs4all.nl>
Jaap W. van Dijk schreef:
> 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.
Date: Sun, 13 Dec 2009 10:58:52 +0100
Message-ID: <4b24bae0$0$22943$e4fe514c_at_news.xs4all.nl>
Jaap W. van Dijk schreef:
> 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.
How about checking if to_char(to_date(varcharstring)) = varcharstring? (Did not test it, just an idea)
Shakespeare Received on Sun Dec 13 2009 - 03:58:52 CST