Oracle PL/SQL Tutorial/Date Timestamp Functions/SYSDATE

Adding One Month

SQL> SELECT ADD_MONTHS(TO_DATE("31-MAR-99"),1) from DUAL;
ADD_MONTH
---------
30-APR-99
SQL>
SQL>

Adding Two Months to SYSDATE

SQL> SELECT ADD_MONTHS(SYSDATE,2) from DUAL;
ADD_MONTH
---------
02-AUG-07
SQL>
SQL> SELECT ADD_MONTHS(SYSDATE,2.654) from DUAL;
ADD_MONTH
---------
02-AUG-07
SQL>
SQL>

Combining TO_CHAR to Format SYSDATE

SQL> SELECT TO_CHAR(SYSDATE,"MM/DD/YYYY HH:MM:SS AM")
  2        "Today"s Date and Time" from DUAL;
Today"s Date and Time
----------------------
06/02/2007 08:06:14 PM
SQL>
SQL>

Default Output of SYSDATE

SQL> SELECT SYSDATE from DUAL;
SYSDATE
---------
02-JUN-07
SQL>

Finding the First Monday After the Current Date and Time

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,"Monday"),"MM/DD/YYYY HH:MM:SS AM")
  2       "Next_Day"
  3   from DUAL;
Next_Day
----------------------
06/04/2007 08:06:15 PM
SQL>
SQL>

last_day(sysdate)

SQL>
SQL>
SQL> select sysdate
  2  ,      last_day(sysdate)       as last_day
  3  from   dual;
SYSDATE   LAST_DAY
--------- ---------
24-JUL-08 31-JUL-08
SQL>

Removing the Time from SYSDATE

SQL> SELECT TO_CHAR(TRUNC(SYSDATE),"MM/DD/YYYY HH:MM:SS AM")
  2      "Today"s Date and Time"
  3       from DUAL;
Today"s Date and Time
----------------------
06/02/2007 12:06:00 AM
SQL>

SYSDATE() returns the current datetime set in the operating system

SQL>
SQL>
SQL> SELECT SYSDATE FROM dual;
SYSDATE
---------
31-MAY-07
SQL>

Truncating to the Quarter

SQL>
SQL>
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,"Q"),"MM/DD/YYYY HH:MM:SS AM")
  2      "Today"s Date and Time"
  3   from DUAL
  4
SQL>