Using sysdate, systimestamp to get the current date and time

SQL> select SYSDATE, SYSTIMESTAMP from dual;

SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------
20-DEC-06
20-DEC-06 10.00.21.921000 AM +08:00

SQL>

SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3 from dual;

SYSDATE PLUS_3
--------- ---------
20-DEC-06 20-MAR-07

SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3Month, ADD_MONTHS(SYSDATE, -2) as Minus2_month
from dual;

SYSDATE PLUS_3MON MINUS2_MO
--------- --------- ---------
20-DEC-06 20-MAR-07 20-OCT-06
SQL>

SQL> select sysdate
2 ,extract(year from sysdate) as year
3 ,extract(month from systimestamp) as month
4 ,extract(timezone_hour from timestamp) as tzh
5 from dual
6
SQL> save extractsysdate
Created file extractsysdate.sql
SQL> edit extractsysdate

select sysdate
,extract(year from sysdate) as year
,extract(month from systimestamp) as month
,extract(timezone_hour from systimestamp) as tzh
from dual
/

SQL> start extractsysdate

SYSDATE YEAR MONTH TZH
--------- ---------- ---------- ----------
20-DEC-06 2006 12 8
SQL>

NUMTODSINTERVAL:

SQL> select sysdate
2 ,sysdate+numtodsinterval(2,'hour') "2 Hours Later"
3 ,sysdate+numtodsinterval(30,'minute') "30 minutes later"
4 from dual
5
SQL> save test_num_tods_interval
Created file test_num_tods_interval.sql

SQL> start test_num_tods_interval

SYSDATE 2 Hours L 30 minute
--------- --------- ---------
20-DEC-06 20-DEC-06 20-DEC-06
SQL>

NUMTOYMINTERVAL:

SQL> select sysdate
2 ,sysdate+numtoyminterval(2,'year') "2 years Later"
3 ,sysdate+numtoyminterval(6,'month') "6 month Later"
4 from dual
5

(then press Enter key 2 times)

SQL> save test_num_toym_interval
Created file test_num_toym_interval.sql

SQL> start test_num_toym_interval

SYSDATE 2 years L 6 month L
--------- --------- ---------
20-DEC-06 20-DEC-08 20-JUN-07
SQL>

NOTE : TO ALTER SESSION TIMESTAMP:

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

OUTPUT AFTER TIMESTAMP ALTER;

SQL> select SYSDATE, SYSTIMESTAMP from dual;

SYSDATE
--------------------
SYSTIMESTAMP
------------------------------------------------
20-DEC-2006 11:32:42
20-DEC-06 11.32.42.671000 AM +08:00
SQL>


SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3 from dual;

SYSDATE PLUS_3
-------------------- --------------------
20-DEC-2006 11:33:19 20-MAR-2007 11:33:19
SQL>


SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3Month, ADD_MONTHS(SYSDATE, -2) as Minus2_month
2 from dual;

SYSDATE PLUS_3MONTH MINUS2_MONTH
-------------------- -------------------- --------------------
20-DEC-2006 11:34:28 20-MAR-2007 11:34:28 20-OCT-2006 11:34:28
SQL>


SQL> start extractsysdate

SYSDATE YEAR MONTH TZH
-------------------- ---------- ---------- ----------
20-DEC-2006 11:35:14 2006 12 8
SQL>


SQL> start test_num_tods_interval

SYSDATE 2 Hours Later 30 minutes later
-------------------- -------------------- --------------------
20-DEC-2006 11:36:14 20-DEC-2006 13:36:14 20-DEC-2006 12:06:14
SQL>

SQL> start test_num_toym_interval

SYSDATE 2 years Later 6 month Later
-------------------- -------------------- --------------------
20-DEC-2006 11:36:55 20-DEC-2008 11:36:55 20-JUN-2007 11:36:55
SQL>