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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats