Re: how to test a string for a valid date in sql

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Fri, 11 Dec 2009 06:01:27 -0800 (PST)
Message-ID: <c50aecf9-831a-4eb1-8672-5d77f7d8946e_at_r5g2000yqb.googlegroups.com>

On 11 Dez., 11:25, "Jaap W. van Dijk" <j.w.vand..._at_hetnet.nl> wrote:
> On Dec 11, 11:05 am, yossarian <yossaria..._at_operamail.com> wrote:

> > 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')?

Hi Jaap,
imho you need some program logic for testing if a varchar is an existing date. The example of yossarian shows you the way in perl.

What about an extra table with all days from the last 200 years ? That will be 70.000 entries. One varchar colum and the corresponding date column. Then you can outer join your table against it and you get a real date or not.

hth
Kay Received on Fri Dec 11 2009 - 08:01:27 CST