Utl_raw.cast_to_date & column low/high values

Anyone attempting to interrogate low and high values in column statistics will quickly discover that there is no built in function to convert raw to date. 

List of other solutions I found:

https://marogel.wordpress.com/2013/04/29/utl_raw-cast_to_date/   - most similar to my solution below. 
https://blog.pythian.com/oracle-internal-datatype-storage/ - Uses intervals. 
http://www.oracle-guy.com/scripts/create_display_raw.sql - uses dbms_stats.convert_raw_value 

I tend to avoid creating objects where possible, so a concise SQL only option is my preferred method for this kind of work. This gets so much easier in 12c now that functions can be defined in the with clause.

The display_raw function linked above can just as easily be embedded in a with statement from in 12c.
See: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

Below is my preferred option for 11g.

Step 1. Find a  value to convert, and dump out its value into its components. 

select table_name, column_name, data_type, low_value, dump(low_value)
from all_tab_cols where data_type = 'DATE'
and low_value is not null and rownum =1  ; 

Result Set 8

TABLE_NAME	COLUMN_NAME	DATA_TYPE	LOW_VALUE	DUMP(LOW_VALUE)
USER$	CTIME	DATE	78730819122608	Typ=23 Len=7: 120,115,8,25,18,38,8

Date is stored in 7 parts.  CCYYMMDDHHMISS.  Split out the 7 parts (numbers 3-9 of the dump string)

select level, 
regexp_substr( 'Typ=23 Len=7: 120,115,8,25,18,38,8' , '[0-9]+', 1, level+2 ) component
from dual connect by rownum <= 7 ; 

Result Set 2

LEVEL	COMPONENT
1	120
2	115
3	8
4	25
5	18
6	38
7	8

First two are stored in exces-100, so we need to subtract 100. 

Last 3 are in excess-1 so need to subtract 1.

- case when level in(1,2) then -100 when level  in(5,6,7) then -1 else 0 end

Convert each of the 7 parts into a two digit char, then use listagg to combine them back into a single value.

listagg(to_char( .... , 'FM999900')) within group(order by level)

My solution: Listagg means it will only work in oracle 11 onwards. 

( select 
  to_date(
    listagg(to_char(
      to_number(  regexp_substr( dump(low_value) , '[0-9]+', 1, level+2 )  , '999')
      - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
    , 'FM00')) within group( order by level  )
  , 'YYYYMMDDHH24MISS')
  from dual connect by rownum <= 7 
) 

As a function this would be:

function raw_to_date(p_date date) return number  is
  v_ret date; 
begin 
  select to_date( 
    listagg(
      to_char(
            to_number(  regexp_substr( dump(p_date) , '[0-9]+', 1, level+2 )  , '999')
              - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
           , 'FM00')
    ) within group( order by level  )
  , 'YYYYMMDDHH24MISS' )  into v_ret 
  from dual connect by rownum <= 7 ;
  return v_ret; 
end; 

Final combined query: 

select 
column_name, data_type, data_length, num_distinct, num_nulls, nullable,
case 
  when data_type in( 'VARCHAR2' ,'VARCHAR','CHAR')  then substr(utl_raw.cast_to_varchar2(low_value), 1, 30)
  when data_type in('FLOAT', 'NUMBER')  then to_char(utl_raw.cast_to_number(low_value), 'FM99999999999990D9999')
  when data_type = 'DATE' then to_char( to_date(( 
     select listagg(to_char(
          to_number(  regexp_substr( dump(low_value) , '[0-9]+', 1, level+2 )  , '999')
          - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
       , 'FM999900')) within group( order by level  )
      from dual connect by rownum <= 7 
    ) , 'YYYYMMDDHH24MISS'), 'DD-Mon-YYYY HH24:MI:SS' )
  else 'NOT SUPPORTED'
end low_value ,
case 
  when data_type in( 'VARCHAR2' ,'VARCHAR','CHAR')  then substr(utl_raw.cast_to_varchar2(high_value), 1, 30)
  when data_type in('FLOAT', 'NUMBER')  then to_char(utl_raw.cast_to_number(high_value), 'FM99999999999990D9999')
  when data_type = 'DATE' then to_char( to_date(( 
     select listagg(to_char(
          to_number(  regexp_substr( dump(high_value) , '[0-9]+', 1, level+2 )  , '999')
          - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
       , 'FM00')) within group( order by level  )
      from dual connect by rownum <= 7 
    ) , 'YYYYMMDDHH24MISS'), 'DD-Mon-YYYY HH24:MI:SS' )
  else 'NOT SUPPORTED'
end high_value 
from all_tab_cols
where owner = '&OWNER.'
and table_name = '&TABLE.'