title: Extracting a portion of a date from a string
body: <p>Basically want to drop the old partition(s) in an Oracle table, currently am having trouble with the SUBSTR function.</p>
  
  <p>Code :</p>
  
  <pre><code>DECLARE
    l_sql_stmt VARCHAR2(1000);
    l_date     DATE;
  
  BEGIN
    FOR x IN (SELECT * 
               FROM user_tab_partitions
               WHERE table_name = 'TABLE_NAME')
    LOOP
      l_date := to_date( substr( x.high_value, 11, 19 ), 'YYYYMMDD' );
      IF( l_date &lt; add_months( trunc(sysdate), -15 ) )
        THEN
          l_sql_stmt := 'ALTER TABLE TABLE_NAME' || ' DROP PARTITION ' || x.partition_name;
          dbms_output.put_line( l_sql_stmt );
          EXECUTE IMMEDIATE l_sql_stmt;
       END IF;
    END LOOP;
  END;
  </code></pre>
  
  <p>The date is present in the column "HIGH_VALUE".
  For example one of the partition's High_value = <code>TO_DATE(' 1950-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')</code></p>
  
  <p>I need the date to be stored in "l_date" variable, so it can be used for further computations.</p>
  
  <p>Error : </p>
  
  <p>ORA-01843: not a valid month</p>
  
  <p>ORA-06512: at line 9</p>
  
  <p>01843.00000 -  "not a valid month"</p>