3

I have a RAW field in my Oracle database that represents the date of user registered in system.

The value is something like 24E2321A0000000000 However I need convert the value to the date it represents (etc 2008-12-25 15:04:31).

I tried with totimestamp (see this sqlfiddle) but that didn't work.

Josh Crozier
  • 233,099
  • 56
  • 391
  • 304
ewan
  • 73
  • 3
  • 12
  • First try to convert this number into decimal and then you will be able to convert it into date.. – The Hungry Dictator Dec 09 '13 at 03:34
  • How you are inserting this date into sql? – Just code Dec 09 '13 at 06:59
  • @dholakiyaankit：It's a legacy system, I don't know why they insert date to RAW feild, for bettter performance ? – ewan Dec 09 '13 at 07:01
  • @Rony: thanks for you quick reply. I tried to convert this "24E2321A0000000000" to number but failed. – ewan Dec 09 '13 at 07:03
  • @user2420342 put your code how your are inserting this date to system – Just code Dec 09 '13 at 07:09
  • @dholakiyaankit：I don't have the code, that's why I try to find a way decode the date from RAW feild. – ewan Dec 09 '13 at 07:12
  • Legacy system how will you identify this date format? – Just code Dec 09 '13 at 07:13
  • how u tried to covert it into decimal just put your code – The Hungry Dictator Dec 09 '13 at 07:17
  • 1
    If it's a proprietary format we can't guess what it is any better than you. Do you have a reason think the value you've shown is the date in 2008? Why do you think it's a date at all if it isn't documented? Do you have source code that inserts records from a UI, which shows how it gets that value? I can't think of any reason to store a date as anything other than a DATE, particularly something so obscure - certainly not for performance. Although I wonder if this used to be in a different RDBMS and when it was moved they copied the internal representation from the old DB as RAW to 'save time'. – Alex Poole Dec 09 '13 at 08:30
  • Please, provide more examples of real data. Can you register new user? With knowledge of actual time new user was registered and having respective raw value for him there are better chances to guess data storage format. – Yaroslav Shabalin Dec 09 '13 at 20:33

2 Answers2

1

Maybe this will help:

SELECT utl_raw.cast_to_binary_integer('24E2321A0000000000') raw_to_int
FROM dual
/

Output is 36. I'm not sure if you need days or hours. Next example is about adding 36 hours to SYSDATE:

-- SYSDATE + 36/24 --
SELECT SYSDATE+(utl_raw.cast_to_binary_integer('24E2321A0000000000')/24) my_date
FROM dual
/

MY_DATE
---------------------
12/13/2013 4:29:22 AM
Art
  • 5,616
  • 1
  • 20
  • 22
1

please try one

declare
d date;
begin
   dbms_stats.convert_raw_value (hextoraw('7876070A010101'), d);
   dbms_output.put_line (d);
end;
AzizD
  • 103
  • 10