CODEWITHSUNDEEP
sqloracle-databasetimestamp
user2902067
user2902067

Reputation: 75

Get Exact timestamp value?

I am trying to subtract some days from 'current_timestamp' and converting that to timestamp using to_timestamp() function in Oracle. But I am always getting start of day time, that is 12 AM.

When I execute 

select to_timestamp(current_timestamp - 3) from dual;

It will give me result like,

18-FEB-14 12.00.00.000000000 AM

But I need exact deduction of 3 days from current time.

Thanks!!!!

Upvotes: 3

Views: 964

Answers (3)

Mr. Kite
Mr. Kite

Reputation: 101

An important difference is that SYSDATE gives you server time, and CURRENT_TIMESTAMP gives you session time.

Also, according to the documentation, TO_TIMESTAMP operates on CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types, not DATE. So I think you need to look elsewhere:

    SELECT CAST (SYSDATE AS TIMESTAMP) from dual;

Upvotes: 2

Nagaraj S
Nagaraj S

Reputation: 13484

select current_timestamp - 3 ts from dual;

or 

SELECT SYSTIMESTAMP - INTERVAL '3' DAY AS day FROM dual;

Upvotes: 3

Brian DeMilia
Brian DeMilia

Reputation: 13248

Will give you time as well:

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 more simply:

select systimestamp - 3 from dual

Upvotes: 2

Related Questions