(UTL_RAW.)CAST_TO_DATE

Tim wrote
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.

Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2

Here is a try to write one, fixes and improvements are welcome !

create or replace function CAST_TO_DATE(bdr in raw) return date deterministic is
begin
  return
     date'1-1-1'
     + NUMTOYMINTERVAL(
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
       'year')
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
  exception when others then return to_date(1, 'J');
end CAST_TO_DATE;   
/