Sokrates on Oracle

(UTL_RAW.)CAST_TO_DATE

Posted by Matthias Rogel on 29. April 2013

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;   
/
About these ads

4 Responses to “(UTL_RAW.)CAST_TO_DATE”

  1. Dom Brooks said

    Why reinvent the wheel? How about DBMS_STATS.CONVERT_RAW_VALUE?
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#ARPLS68498

    (And as commented on Bertrand’s post, for peeked binds you could use DBMS_SQLTUNE.EXTRACT_BIND/EXTRACT_BINDS)

  2. bdrouvot said

    Hello Matthias,

    Did you test it on big endian and little endian ?

    Thx
    Bertrand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: