title: date check to avoid exception in sql
body: <p>I have a query, where I need to apply the TO_DATE(format specified is 'DD.MM.YYYY') function to a VARCHAR type column. However, it is possible that the date may be invalid
  e.g. 'aa.12.2012' or '31.02.2012'  etc</p>
  
  <p>This will lead to and exception when TO_DATE function processes the faulty date value and the query will fail.  </p>
  
  <p>One way to do it is to run cursor and identify every cell with faulty date, but I want to avoid this way.</p>
  
  <p>I tried to work with regular expression, but was I did not succeed to place all validations.</p>
  
  <p>It was like this</p>
  
  <p><pre><code>
  select
   case
     when regexp_like('24.01.2013',
                      '[[:digit:]]{2}.[[:digit:]]{2}.[[:digit:]]{4}') then
      1
     else
      0
   end chk
    from dual
  </pre></code></p>
  
  <p>This was not able to catch invalid dates like 31st of Feb.</p>
  
  <p>Is it possible to do all validation in the regular expression for date, because I performed all validations via decode &amp; case and it is rather lengthy.</p>
  
  <p><pre><code>
  SELECT DECODE((REPLACE(TRANSLATE('29.01.2013', '0123456789.', ' '), ' ', '')),
                NULL,
                (CASE
                  WHEN LENGTH('29.01.2013') != 10 THEN
                   'FALSE'
                  WHEN LENGTH('29.01.2013') -
                       LENGTH(REPLACE(TRANSLATE('29.01.2013', '.', ' '),
                                      ' ',
                                      '')) != 2 THEN
                   'FALSE'
                  WHEN INSTR('29.01.2013', '.', 1, 1) != 3 THEN
                   'FALSE'
                  WHEN INSTR('29.01.2013', '.', 1, 2) != 6 THEN
                   'FALSE'
                  WHEN TO_NUMBER(SUBSTR('29.01.2013', 4, 2)) IN
                       (1, 3, 5, 7, 8, 10, 12) THEN
                   (CASE
                  WHEN TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) >= 1 AND
                       TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) &lt;= 31 THEN
                   'TRUE'
                  ELSE
                   'FALSE'
                END) WHEN
                TO_NUMBER(SUBSTR('29.01.2013', 4, 2)) IN (4, 6, 9, 11)
                THEN(CASE
                       WHEN TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) >= 1 AND
                            TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) &lt;= 30 THEN
                        'TRUE'
                       ELSE
                        'FALSE'
                     END) WHEN TO_NUMBER(SUBSTR('29.01.2013', 4, 2)) IN (2)
                THEN(CASE
                       WHEN REMAINDER(TO_NUMBER(SUBSTR('29.01.2013', 7, 4)), 4) = 0 THEN
                        (CASE
                       WHEN TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) >= 1 AND
                            TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) &lt;= 29 THEN
                        'TRUE'
                       ELSE
                        'FALSE'
                     END) WHEN
                REMAINDER(TO_NUMBER(SUBSTR('29.01.2013', 7, 4)), 4) != 0
                THEN(CASE
                       WHEN TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) >= 1 AND
                            TO_NUMBER(SUBSTR('29.01.2013', 1, 2)) &lt;= 28 THEN
                        'TRUE'
                       ELSE
                        'FALSE'
                     END) END) END), 'FALSE')
    FROM DUAL
  </pre></code></p>
  
  <p>Thanks in advance</p>