How to use the Oracle Timestamp Data Type?

Requirement:
I want to fetch the data which after 3 days
My Table column CREATETIMESTAMP which have Timestemp Data type

SELECT * FROM MYTABLE
WHERE CREATETIMESTAMP <= CURRENT_TIMESTAMP + 3

SQL> select current_timestamp,current_timestamp + 3 day from dual;

CURRENT_TIMESTAMP                       DAY
----------------------------------------------------------------
07-NOV-12 03.10.08.304701 PM +05:30     10-NOV-12

We are not got the time comparison.

Solution:-

SELECT * FROM MYTABLE
WHERE CREATETIMESTAMP <= CURRENT_TIMESTAMP + INTERVAL ‘3’ day;

SQL>

select current_timestamp,current_timestamp + INTERVAL '3' day from dual;

CURRENT_TIMESTAMP                              CURRENT_TIMESTAMP+INTERVAL'3'DAY
---------------------------------------------------------------------------
07-NOV-12 03.10.00.222181 PM +05:30            10-NOV-12 03.10.00.222181000 PM +05:30

1 row selected.

Reference
http://www.morganslibrary.org/reference/timestamp.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s