Hi to all, Need help can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour is incorrect.Need help. Code (Text): SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' || to_char(to_date(mod (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test, systimestamp,tb1.dte_computation_on_data from ( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data FROM dual)tb1; results --------------------------------------------------------------------------------------- REVERT_TEST SYSTIMESTAMP DTE_COMPUTATION_ON_DATA 05/19/2011 03:46:18 5/19/2011 11:46:18.005171 AM +08:00 1305776778

It's not off, look at the result from systimestamp. SYSTIMESTAMP is defined as a TIMESTAMP WITH TIME ZONE type so it's adding the timezone offset to the original hour (3 am + 8 hour offset = 11 AM).

You're welcome, and I understand that at first examination all you saw was the time. It helps to know how such values (systimestamp) are defined so the results can be properly interpreted.