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

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sat, 12 Dec 2009 14:16:55 +0100
Message-ID: <4b23975d$0$2860$ba620e4c_at_news.skynet.be>

Jaap W. van Dijk schreef:
> On Dec 11, 11:30 am, yossarian<yossaria..._at_operamail.com> wrote:
>> Jaap W. van Dijk wrote:
>>
>>> Is it possible to test with format_regexp if ('31112009', 'ddmmyyyy')
>>> is a valid date, or ('29022000', 'ddmmyyyy')?
>>
>> http://www.regular-expressions.info/dates.html
>
> Thanks for the pointer. I will use a case statement similar to the
> perl alternative on this page. It is less complicated than I thought
> it would be.
>
> Regards,
> Jaap.

Zoals toen we jong waren:

select

   ds.*,
   case

     when maand in (1,3,5,7,8,10,12) and dag between 1 and 31
       or maand in (4,6,9,11) and dag between 1 and 30
       or maand = 2 and dag between 1 and 28
       or maand = 2 and dag = 29
          and mod(jaar,4) = 0 and (mod(jaar,100) != 0 or mod(jaar,400) = 0)
     then to_date(dag||maand||jaar,'DDMMYYYY')
     else to_date('01011999','DDMMYYYY')

   end
   from (

     select
       substr(datum,1,2) dag,
       substr(datum,3,2) maand,
       substr(datum,5,4) jaar
       from (
         select
           case when regexp_like(d.datum,'[0-9]{8}')
             then d.datum
             else '31122009'
           end datum
           from (
             select '29022009' datum from dual
             union all
             select '31042009' datum from dual
             union all
             select '29022008' datum from dual
             union all
             select '29022000' datum from dual
             union all
             select '29021900' datum from dual
             union all
             select '00022000' datum from dual
             union all
             select '01022009' datum from dual
           ) d
       )

   ) ds
/ Received on Sat Dec 12 2009 - 07:16:55 CST