We're trying to report on the log database. In tables like AU_TRANSACTIONLOG we see what seem to be date fields, like 'AU_LOGDATE' are specified to be Decimal(18, 0). The values stored in this field are like '1404098683835'. How do I convert or cast this field so I can search for it with a date string and have it returned as a recognizable date?
The date is stored in java milliseconds. You can follow the instructions here to pull the date in a readable format
select to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
(1126483200000)/1000/60/60/24 , 'YYYY-MM-DD HH12:MI:SS am') datestr from dual
This site will allow you to translate the value in realtime