SQL Syntax to Add Offset Hour, Minute, Year, Day, Month to Oracle Timestamp Values Examples

Syntax to add Hour, Minute, Second, Year, Day and Month to the TIMESTAMP value is
timestampvalue + INTERVAL ‘<value to be added>’ TYPE
where TYPE can be
YEAR,
MONTH,
DAY,
HOUR,
SECOND,
TIMEZONE_HOUR,
TIMEZONE_MINUTE,
TIMEZONE_REGION,
TIMEZONE_ABBR

See Examples Here.

Adding 1 Year:
SQL> SELECT SYSTIMESTAMP currdate, SYSTIMESTAMP + INTERVAL '1' YEAR CurrPlusYEAR FROM DUAL;

CURRDATE                            CURRPLUSYEAR
----------------------------------- --------------------------------------
10-MAY-12 02.00.48.223548 AM -04:00 10-MAY-13 02.00.48.223548000 AM -04:00


SQL> SELECT TO_TIMESTAMP('10-MAY-12 02:04:18','DD-MON-YY HH:MI:SS') TVALUE, TO_TIMESTAMP('10-MAY-12 02:04:18','DD-MON-YY HH:MI:SS')+ INTERVAL '1' YEAR  CurrPlusYEAR FROM DUAL;


TVALUE                              CURRPLUSYEAR
----------------------------------- -----------------------------------
10-MAY-12 02.04.18.000000000 AM     10-MAY-13 02.04.18.000000000 AM

Adding 1 Month:
SQL> SELECT SYSTIMESTAMP currdate, SYSTIMESTAMP + INTERVAL '1' MONTH CurrPlusMONTH FROM DUAL;

CURRDATE                            CURRPLUSMONTH
----------------------------------- --------------------------------------
10-MAY-12 02.01.38.481009 AM -04:00 10-JUN-12 02.01.38.481009000 AM -04:00

Adding 1 Day
SQL> SELECT SYSTIMESTAMP currdate, SYSTIMESTAMP + INTERVAL '1' DAY CurrPlusDAY FROM DUAL;

CURRDATE                            CURRPLUSDAY
----------------------------------- --------------------------------------
10-MAY-12 02.02.20.928457 AM -04:00 11-MAY-12 02.02.20.928457000 AM -04:00

Adding 1 Hour
SQL> SELECT SYSTIMESTAMP currdate, SYSTIMESTAMP + INTERVAL '1' HOUR CurrPlusHOUR FROM DUAL;

CURRDATE                            CURRPLUSHOUR
----------------------------------- --------------------------------------
10-MAY-12 02.02.47.866182 AM -04:00 10-MAY-12 03.02.47.866182000 AM -04:00
Adding 1 Minute
SQL> SELECT SYSTIMESTAMP currdate, SYSTIMESTAMP + INTERVAL '1' MINUTE CurrPlusMINUTE FROM DUAL;

CURRDATE                            CURRPLUSMINUTE
----------------------------------- --------------------------------------
10-MAY-12 02.04.18.618107 AM -04:00 10-MAY-12 02.05.18.618107000 AM -04:00
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google