Using Datetime Functions in Queries

Datetime functions operate on date, timestamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

To understand the following example, you must understand the JOB_HISTORY table.

When an employee changes jobs, the START_DATE and END_DATE of his or her previous job are recorded in the JOB_HISTORY table. Employees who have changed jobs more than once have multiple rows in the JOB_HISTORY table, as the following query and its results show:

SELECT * FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;

Result:

EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
        101 21-SEP-89 27-OCT-93 AC_ACCOUNT           110
        101 28-OCT-93 15-MAR-97 AC_MGR               110
        102 13-JAN-93 24-JUL-98 IT_PROG               60
        114 24-MAR-98 31-DEC-99 ST_CLERK              50
        122 01-JAN-99 31-DEC-99 ST_CLERK              50
        176 24-MAR-98 31-DEC-98 SA_REP                80
        176 01-JAN-99 31-DEC-99 SA_MAN                80
        200 17-SEP-87 17-JUN-93 AD_ASST               90
        200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
        201 17-FEB-96 19-DEC-99 MK_REP                20
 
10 rows selected.

The query in the following example uses the MONTHS_BETWEEN function to show how many months each employee held each of his or her previous jobs. For information about the MONTHS_BETWEEN function, see Oracle Database SQL Language Reference.

Displaying the Number of Months Between Dates

SELECT e.EMPLOYEE_ID,
e.LAST_NAME,
TRUNC(MONTHS_BETWEEN(j.END_DATE, j.START_DATE)) "Months Worked"
FROM EMPLOYEES e, JOB_HISTORY j
WHERE e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY "Months Worked";

Result:

EMPLOYEE_ID LAST_NAME                 Months Worked
----------- ------------------------- -------------
        176 Taylor                                9
        122 Kaufling                             11
        176 Taylor                               11
        114 Raphaely                             21
        101 Kochhar                              40
        201 Hartstein                            46
        101 Kochhar                              49
        200 Whalen                               53
        102 De Haan                              66
        200 Whalen                               69
 
10 rows selected.

The query in the following example uses the EXTRACT and SYSDATE functions to show how many years each employee in department 100 has been employed. For more information about the SYSDATE function, see Oracle Database SQL Language Reference. For more information about the EXTRACT function, see Oracle Database SQL Language Reference.

Displaying the Number of Years Between Dates

SELECT LAST_NAME,
(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";

Result:

LAST_NAME                 Years Employed
------------------------- --------------
Popp                                   9
Urman                                 10
Chen                                  11
Sciarra                               11
Greenberg                             14
Faviet                                14
 
6 rows selected.

Suppose that an employee receives his or her first check on the last day of the month in which he or she was hired. The query in the following example uses the LAST_DAY function to show the first pay day for each employee in department 100. For information about the LAST_DAY function, see Oracle Database SQL Language Reference.

Displaying the Last Day of a Selected Month

SELECT LAST_NAME,
HIRE_DATE "Hired",
LAST_DAY(HIRE_DATE) "Paid"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

LAST_NAME                 Hired     Paid
------------------------- --------- ---------
Faviet                    16-AUG-94 31-AUG-94
Greenberg                 17-AUG-94 31-AUG-94
Chen                      28-SEP-97 30-SEP-97
Sciarra                   30-SEP-97 30-SEP-97
Urman                     07-MAR-98 31-MAR-98
Popp                      07-DEC-99 31-DEC-99
 
6 rows selected.

Suppose that an employee receives his or her first evaluation six months after being hired. The query in the following example uses the ADD_MONTHS function to show the first evaluation day for each employee in department 100. For information about the ADD_MONTHS function, see Oracle Database SQL Language Reference.

Displaying a Date Six Months from a Selected Date

SELECT LAST_NAME,
HIRE_DATE "Hired",
ADD_MONTHS(HIRE_DATE, 6) "Evaluated"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

LAST_NAME                 Hired     Evaluated
------------------------- --------- ---------
Faviet                    16-AUG-94 16-FEB-95
Greenberg                 17-AUG-94 17-FEB-95
Chen                      28-SEP-97 28-MAR-98
Sciarra                   30-SEP-97 31-MAR-98
Urman                     07-MAR-98 07-SEP-98
Popp                      07-DEC-99 07-JUN-00
 
6 rows selected.

The query in the following example uses the SYSTIMESTAMP function to display the current system time and date. SYSTIMESTAMP is similar to SYSDATE, but it returns more information. For information about the SYSTIMESTAMP function, see Oracle Database SQL Language Reference.

The table in the FROM clause of the query, DUAL, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from DUAL when you want to compute a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. For more information about selecting from DUAL, see Oracle Database SQL Language Reference.

Displaying System Date and Time

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;

Results depend on current SYSTIMESTAMP value, but have this format:

System Time and Date
-------------------------------------------------------------------
18:47:33, 6/19/2008

Using Datetime Functions in Queries

Related Topics