Using Date Functions

Oracle Database Express Edition provides a set of date functions to manipulate and calculate date and time data. For example, with date functions you can add months to, extract a specific field from, truncate, and round a date value. You can also calculate the number of months between two dates.

Example: Using SQL Date Functions shows the use of some date functions on date data.

Using SQL Date Functions

-- in the following statement you can use MONTHS_BETWEEN to compute months
-- employed for employees and then truncate the results to the whole month
-- note the use of the label (alias) "Months Employed" for the computed column
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed" 
  FROM employees;

-- the following extracts displays the year hired for each employee ID
SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;

-- the following extracts and concatenates the year, month, and day from SYSDATE
SELECT EXTRACT(YEAR FROM SYSDATE) || EXTRACT(MONTH FROM SYSDATE) || 
   EXTRACT(DAY FROM SYSDATE) "Current Date" FROM DUAL;

-- the following adds 3 months to the hire_date of an employee
SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3) FROM employees;

-- LAST_DAY finds the last day of the month for a specific date, such as hire_date
SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month" 
   FROM employees;

-- the following returns the system date, including fractional seconds 
-- and time zone, of the system on which the database resides
SELECT SYSTIMESTAMP FROM DUAL;