CODEWITHSUNDEEP
sqloracle-databasetimestampdate-arithmetic
Nadir
Nadir

Reputation: 221

Add Day to Timestamp

How do I add days to a timestamp? If my timestamp is 01-JAN-2011 11-09-05 and I add 2 days, I want 03-JAN-2011 11-09-05.

Upvotes: 22

Views: 67742

Answers (3)

adcelis
adcelis

Reputation: 76

In a similar case, I used:

SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + NUMTODSINTERVAL(2, 'DAY')

Because, othewise, the expression is converted to DATE and precission is lost. See: NUMTODSINTERVAL documentation

Upvotes: 2

DCookie
DCookie

Reputation: 43523

A completely Oracle-centric solution is to simply add 2 to the timestamp value as the default interval is days for Oracle dates/timestamps:

SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + 2
  FROM dual;

Upvotes: 4

Marc B
Marc B

Reputation: 360572

select '01-jan-2011 11-09-05' + interval '2' day

Upvotes: 38

Related Questions