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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google