Oracle:Date functions

Main Page >> Oracle and SQL >> Workbook >> Functions >> Date Functions

This page looks at working with data values.

Date Arithmetic

You can perform arithmetic operations on date fields.

The operations you can use are:

date + number

date - number

date - date

To find the review dates of employees in department 20 where the review occurs one year after the hiredate:

SELECT  ENAME,  HIREDATE,  HIREDATE  +  365  AS REVIEW 
FROM  EMP 
WHERE  DEPTNO  =  20;

Note, AS REVIEW will give the new derived column a name. If omitted, Oracle will use the expression instead. The AS column-name clause can be used after any column name if you wish to rename it.

Date Functions

The following table lists some date functions available in Oracle (where D, D1, D2 are date fields or date strings and N is an integer):

Function	Example	Results
ADD_MONTHS(D,N)	ADD_MONTHS(hiredate,3)	date D plus N months. Use a negative number to subtract months
GREATEST(D1,D2)	GREATEST(hiredate, '11-JUL-2014')	later of D1 and D2 (column names usually used here)
LEAST(D1,D2)	LEAST(hiredate,'01-JAN-2014')	earlier of D1 and D2 (column names usually used here)
LAST_DAY(D1)	LAST_DAY('01-FEB-2014')	date of the last day of given month
MONTHS_BETWEEN(D1,D2)	MONTHS_BETWEEN(sysdate, hiredate)	number of months between D1 and D2
NEXT_DAY(D, 'a day')	NEXT_DAY('01-JAN-2014', 'FRIDAY')	date of next particular day of week after given date
TO_CHAR(D,'date_format')	TO_CHAR(hiredate, 'MM/DD/YY')	changes format displayed to MM/DD/YY
SYSDATE	SYSDATE	Current date and time

There are a number of other date conversion formats that can be used with TO_CHAR. Given a date of birth column called dob and the value of the date being converted is: 25-DEC-15, some examples are:

Date Format	Example	Result
Date without TO_CHAR	dob	25-DEC-15
MM/DD/YY	TO_CHAR(dob,'MM/DD/YY')	12/25/15
DD.MM.YYYY	TO_CHAR(dob,'DD.MM.YYYY')	25.12.2015
Month DD, YYYY	TO_CHAR(dob,'Month DD, YYYY')	December 25, 2015
DY DD MON YY	TO_CHAR(dob,'DY DD MON YY')	FRI 25 DEC 15
Day Mon DD	TO_CHAR(dob,'Day Mon DD')	Friday Dec 25
Day Month DD, YYYY	TO_CHAR(dob,'Day Month DD, YYYY')	Friday December 25, 2007
DY DD Month YYYY	TO_CHAR(dob,'DY DD Month YYYY')	FRI 25 December 2007
ddth "of" Month	TO_CHAR(dob,'ddth "of" Month')	25th of December
HH:MI:SS	TO_CHAR(dob,'HH:MI:SS')	12:30:29 (retrieves time element)

Note, always insert dates using the 4-digit year format, otherwise you may find ’10-AUG-11’ becomes ’10-AUG-1911’!

Example Queries Involving Date Functions

To find the review dates of employees hired in the last 90 days where reviews take place one year after hire:

SELECT  ENAME,  TO_CHAR(HIREDATE ,'MM/DD/YYYY') AS HIRE_DATE, 
   TO_CHAR(SYSDATE, 'HH:MI:SS') AS TIME, 
   TO_CHAR(HIREDATE  +  365, 'ddth "of" Month') AS REVIEW_DATE
FROM  EMP  
WHERE  HIREDATE  +  90  >  SYSDATE;

How many days are left in the current month:

SELECT SYSDATE, LAST_DAY(SYSDATE) AS "Last", LAST_DAY(SYSDATE) - SYSDATE AS "Days Left"
FROM DUAL; 

DUAL is a special one-row, one-column "dummy" table that can be used when performing calculations, or retrieve system data, as above, rather than extracting data from any particular user-defined tables.

Show the earliest, latest and last date of the hiredates and current dates in the emp table:

SELECT LEAST(HIREDATE, SYSDATE) AS EARLIER, 
  GREATEST(HIREDATE, SYSDATE) AS LATEST, 
  LAST_DAY(HIREDATE) AS LAST_DAY FROM EMP; 

To list each employee's hiredate and review date, where the review date occurs 6 months after hiredate:

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,6) AS review_date
FROM EMP;

List each employee's name and month of hire only:

SELECT ENAME, TO_CHAR(HIREDATE,'MON') AS MONTH FROM EMP;

Inserting Date Values

When you enter a value into a date field, it must be in the standard format (DD-MON-YY). Character expression can be converted by using the TO_DATE function.

For example:

INSERT  INTO  EMP  
  VALUES  (7657,  'MASON',  'ANALYST',  7566, TO_DATE('11/07/2015', 'DD/MM/YYYY'),  3400,  0,  20);
COMMIT;

The Current Date

To enter the current date, use the SYSDATE system variable.

To enter a new employee just hired:

INSERT INTO EMP  
  VALUES  (7659,  'FOX',  'ANALYST', 7566, SYSDATE, 2900, 0, 20);
COMMIT;

To list this new employee's name and hiredate, with the hiredate shown in the format of the Month in full, day and year:

SELECT ENAME, TO_CHAR(HIREDATE,'DDth MONTH YYYY') AS HIREDATE 
FROM EMP 
WHERE EMPNO = 7659;

Note, Oracle's date is actually a DATETIME. This means the time element is also stored with the date.

If you tried to retrieve the record for Fox this way:

SELECT ENAME, TO_CHAR(HIREDATE,'DDth MONTH YYYY') AS HIREDATE
FROM EMP
WHERE HIREDATE = SYSDATE;

You will get no rows selected because the time element will have moved on, no matter how fast you can type!

To retrieve records added today, you need to strip out the time part when comparing the columns, but lets see what the time is:

SELECT ENAME, TO_CHAR(HIREDATE,'DDth MONTH YYYY hh:mi:ss') AS HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'DD-MON-YY') = TO_CHAR(SYSDATE,'DD-MON-YY');

As you can see the time by default will be when you inserted the record.

Exercise 5.1

5.1 Insert a new employee to department 20, with a hiredate of the 1st January 2016 at 9am. Add appropriate values for the other columns.

Next Step

Character functions.