I am trying to subtract a few days from 'current_timestamp' and convert this to timestamp using the to_timestamp () function in Oracle. But I always get the start of daytime, i.e. 12 hours.
When i do
select to_timestamp(current_timestamp - 3) from dual;
This will give me a result like
18-FEB-14 12.00.00.000000000 AM
But I need an accurate subtraction of 3 days from the current time.
Thank!!!!
select current_timestamp - 3 ts from dual;
or
SELECT SYSTIMESTAMP - INTERVAL '3' DAY AS day FROM dual;
Also give you time:
select sysdate - 3 from dual;
Edit based on your comment:
select to_timestamp(to_char(sysdate-3,'DD-Mon-RR HH24:MI:SS'),'DD-Mon-RR HH24:MI:SS') from dual;
Or easier:
select systimestamp - 3 from dual
, SYSDATE , CURRENT_TIMESTAMP .
, TO_TIMESTAMP CHAR, VARCHAR2, NCHAR NVARCHAR2, DATE. , :
SELECT CAST (SYSDATE AS TIMESTAMP) from dual;