Oracle 11g Sql convert date from blob field

Question

I have a problem in converting a date value stored in a blob field in Oracle 11g sql command. When i execute the sql:

select dump(HIGH_VALUE) from all_tab_columns where COLUMN_NAME='TARIH'

i receive the following result;

Typ=23 Len=7: 120,116,3,6,1,1,1

I know that these numbers represent a date (not datetime), but i don't know how to extract the date from this result.

Thanks in advance, Alper

Answer 1

Oracle stores dates in tables as 7-bytes

byte 1 - century + 100
byte 2 - (year MOD 100 ) + 100
byte 3 - month
byte 4 - day
byte 5 - hour + 1
byte 6 - minute + 1
byte 7 - seconds+ 1

So 120,116,3,6,1,1,1 converts to:

byte 1 - century = 120 - 100 = 20
byte 2 - year = 116 - 100 = 16
byte 3 - month = 3
byte 4 - day = 6
byte 5 - hour = 1 - 1 = 0
byte 6 - minute = 1 - 1 = 0
byte 7 - seconds = 1 - 1 = 0

So 2016-03-06T00:00:00

Oracle Setup:

CREATE TABLE file_upload ( file_blob BLOB );

INSERT INTO file_upload VALUES (
  utl_raw.cast_to_raw(
    CHR(120) || CHR(116) || CHR(3) || CHR(6) || CHR(1) || CHR(1) || CHR(1)
  )
);

Query:

SELECT DUMP( DBMS_LOB.SUBSTR( file_blob, 7, 1 ) ) AS dmp,
       TO_DATE(
         TO_CHAR(
           ( ASCII( SUBSTR( chars, 1, 1 ) ) - 100 ) * 100
             + ASCII( SUBSTR( chars, 2, 1 ) ) - 100,
           '0000'
         )
         || TO_CHAR( ASCII( SUBSTR( chars, 3, 1 ) ), '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 4, 1 ) ), '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 5, 1 ) ) - 1, '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 6, 1 ) ) - 1, '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 7, 1 ) ) - 1, '00' ),
         'YYYYMMDDHH24MISS'
       ) AS converted_date
FROM   (
  SELECT file_blob,
         UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR( file_blob, 7, 1 ) ) AS chars
  FROM   file_upload
);

Output:

DMP                             CONVERTED_DATE    
------------------------------- -------------------
Typ=23 Len=7: 120,116,3,6,1,1,1 2016-03-06 00:00:00