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

From: Kay Kanekowski <>
Date: Fri, 11 Dec 2009 06:01:27 -0800 (PST)
Message-ID: <>

On 11 Dez., 11:25, "Jaap W. van Dijk" <> wrote:
> On Dec 11, 11:05 am, yossarian <> 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.

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

Original text of this message