Determining the Years Between Dates
Retrieving Data with Queries
Querying and Manipulating Data 2-19
Jonathon Taylor 11
...
10 rows selected
You will notice that this result shows that of the employees who left the company, the
shortest and the longest stays were 9 and 69 months, respectively.
In
, you will use the
EXTRACT
function to determine if employees are in
their sixth calendar year of continuous employment. The
EXTRACT
function can also
be used in combination with
MONTH
,
DATE
, and so on.
Note that the
SYSDATE
function gives the current date of the system clock. See Oracle
Database SQL Language Reference for information about the
SYSDATE
function.
Example 227 Determining the Years Between Dates
SELECT first_name || ' ' || last_name "Name",
(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed"
FROM employees;
The results of the query appear.
Name Years Employed
---------------------------------------------- --------------
Steven King 20
Neena Kochhar 18
Lex De Haan 14
...
107 rows selected
You will notice that this result shows that employee 'Steven King' has worked for the
company the longest, 20 years.
In
, you will use the
last_day
function to determine the last day of the
month in which an employee was hired.
Example 228 Getting the Last Day of the Month for a Specified date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
LAST_DAY(hire_date) "End of Month"
FROM employees;
The results of the query appear.
Name Date Started End of Month
------------------------------ ------------------ -------------------------
Steven King 17-JUN-87 30-JUN-87
Neena Kochhar 21-SEP-89 30-SEP-89
Lex De Haan 13-JAN-93 31-JAN-93
...
107 rows selected
You will notice that this result shows the correct end of the month for each
hire_
date
value.
In
, you will use the
ADD_MONTHS
function to add
6
months to the date
on which an employee was hired. See Oracle Database SQL Language Reference for
information about the
ADD_MONTH
function.
Example 229 Adding Months to a Date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
