Oracle PL/SQL Tutorial/Date Timestamp Functions/Introduction

Date Functions

We must format dates to see all of the information contained in a date column.

We use the TO_CHAR function to do the converting.

   <source lang="sql">

SQL> SELECT SYSDATE FROM dual; SYSDATE

---

29-MAY-07 SQL></source>

Differences Between Dates

   <source lang="sql">

SQL> SQL> CREATE TABLE dept (

 2     deptID INT NOT NULL PRIMARY KEY,
 3     StudentID    INT NOT NULL,
 4     ClassID      INT NOT NULL,
 5     EnrolledOn   DATE,
 6     Grade        INT);

Table created. SQL> SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE "2002-09-20",70); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE "2002-09-20",60); 1 row created. SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (10,5,1,DATE "2002-09-23",33); 1 row created. SQL> SQL> SQL> SELECT StudentID,

 2         FLOOR(MAX(CURRENT_DATE - EnrolledOn)) AS DaysEnrolled
 3  FROM dept
 4  GROUP BY StudentID
 5  ORDER BY DaysEnrolled DESC;
STUDENTID DAYSENROLLED

---

------------

        4         2593
        1         2590
        5         2590
        3         2583
        2         2225

5 rows selected. SQL> SQL> SQL> drop table dept; Table dropped.</source>

LAST_DAY, TO_CHAR, TO_DATE

   <source lang="sql">

SQL> SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE("23SEP2006","ddMONyyyy")), "Month dd, yyyy") FROM dual; TO_CHAR(LAST_DAY(T

---

September 30, 2006 SQL> SQL></source>

select date "1954-08-0 as 10,000 days

   <source lang="sql">

SQL> SQL> SQL> select date "1954-08-11" + 10000 as "10,000 days"

 2  from   dual;

10,000 da

---

27-DEC-81 SQL></source>

Time Interval Related Functions

Function Description NUMTODSINTERVAL(x, interval_unit) Converts the number x to an INTERVAL DAY TO SECOND with the interval for x supplied in interval_unit, which you may set to DAY, HOUR, MINUTE, or SECOND. NUMTOYMINTERVAL(x, interval_unit) Converts the number x to an INTERVAL YEAR TO MONTH with the interval for x supplied in interval_unit, which you may set to YEAR or MONTH. TO_DSINTERVAL(x) Converts the string x to an INTERVAL DAY TO SECOND. TO_YMINTERVAL(x) Converts the string x to an INTERVAL YEAR TO MONTH.

Timestamp-Related Functions

Function Description CURRENT_TIMESTAMP() Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone. EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x; x may be one of the timestamp types or a DATE. FROM_TZ(x, time_zone) Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE. LOCALTIMESTAMP() Returns a TIMESTAMP containing the current time in the session time zone. SYSTIMESTAMP() Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone. SYS_EXTRACT_UTC(x) Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC. TO_TIMESTAMP(x, [format]) Converts the string x to a TIMESTAMP. You may also specify an optional format for x. TO_TIMESTAMP_TZ(x, [format]) Converts the string x to a TIMESTAMP WITH TIMEZONE. You may also specify an optional format for x.

Using Datetime Functions

In the following table, x represents a datetime or a timestamp.

Function Description ADD_MONTHS(x, y) Add y months to x. If y is negative, y months are subtracted from x. LAST_DAY(x) Get the last day of the month. MONTHS_BETWEEN(x, y) Returns the number of months between x and y. If x appears before y on the calendar, the number returned is positive. NEXT_DAY(x, day) Returns the datetime of the next day following x; day is specified as a literal string, for example SATURDAY. NEW_TIME Returns the time/day value from a time zone specified by the user. ROUND(x [, unit]) Rounds x. By default, x is rounded to the beginning of the nearest day. You may supply an optional unit string to indicate the rounding unit. SYSDATE() Returns the current datetime set for the operating system. TRUNC(x [, unit]) Truncates x. By default, x is truncated to the beginning of the day. You may supply an optional unit string that indicates the truncating unit.