Q:

Extracting a portion of a date from a string

Basically want to drop the old partition(s) in an Oracle table, currently am having trouble with the SUBSTR function.
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 < 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;

The date is present in the column "HIGH_VALUE".
For example one of the partition's High_value = TO_DATE(' 1950-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
I need the date to be stored in "l_date" variable, so it can be used for further computations.
Error : 
ORA-01843: not a valid month
ORA-06512: at line 9
01843.00000 -  "not a valid month"

A:

If high_value equals the following (which is what I think you're saying): TO_DATE(' 1950-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), then the following line of code is wrong:
l_date := to_date( substr( x.high_value, 11, 19 ), 'YYYYMMDD' );

The reason that it is wrong is because the mask YYYYMMDD is wrong. SUBSTR( x.high_value, 11, 19) will return the following:
1950-01-01 00:00:00

So you want to use YYYY-MM-DD HH24:MI:SS as your mask:
l_date := to_date( substr( x.high_value, 11, 19 ), 'YYYY-MM-DD HH24:MI:SS' );

or perhaps SYYYY-MM-DD (as in high_value itself), assuming that the year must be signed:
l_date := to_date( substr( x.high_value, 11, 19 ), 'SYYYY-MM-DD HH24:MI:SS' );

It might be worth your while to try and extract the mask from high_value (using, for example, a regex) and use that!
UPDATE This will work to capture the mask, assuming that high_value is of the same general format:
-- Try to capture the mask
WITH x AS (
    SELECT 'TO_DATE('' 1950-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')' AS high_value
      FROM dual
)
SELECT REGEXP_SUBSTR(x.high_value, '[^'']+', INSTR(x.high_value, '''', 1, 3))
  FROM x

So you might use the following in your code:
l_date := TO_DATE( SUBSTR( x.high_value, 11, 19 ), REGEXP_SUBSTR( x.high_value, '[^'']+', INSTR( x.high_value, '''', 1, 3 ) ) );

Hope this helps.

