Oracle PL/SQL Tutorial/Date Timestamp Functions/SYSDATE

Adding One Month

   <source lang="sql">

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

---

30-APR-99 SQL> SQL></source>

Adding Two Months to SYSDATE

   <source lang="sql">

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></source>

Combining TO_CHAR to Format SYSDATE

   <source lang="sql">

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></source>

Default Output of SYSDATE

   <source lang="sql">

SQL> SELECT SYSDATE from DUAL; SYSDATE

---

02-JUN-07 SQL></source>

Finding the First Monday After the Current Date and Time

   <source lang="sql">

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></source>

last_day(sysdate)

   <source lang="sql">

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></source>

Removing the Time from SYSDATE

   <source lang="sql">

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></source>

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

   <source lang="sql">

SQL> SQL> SQL> SELECT SYSDATE FROM dual; SYSDATE

---

31-MAY-07 SQL></source>

Truncating to the Quarter

   <source lang="sql">

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></source>