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,'MM/DD/YY')	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. If the date being used is: 25-DEC-15, some examples are:

Format	Display
blank (without TO_CHAR)	25-DEC-15
MM/DD/YY	12/25/15
DD.MM.YYYY	25.12.2015
Month DD, YYYY	December 25, 2015
DY DD MON YY	FRI 25 DEC 15
Day Mon DD	Friday Dec 25
Day Month DD, YYYY	Friday December 25, 2007
DY DD Month YYYY	FRI 25 December 2007
ddth "of" Month	25th of December
HH:MM: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’! 1.3 Some 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’), TO_CHAR(SYSDATE, ‘HH:MM:SS’) TIME, TO_CHAR(HIREDATE + 365, ‘ddth “of” Month’) FROM EMP WHERE HIREDATE + 90 > SYSDATE; How many days are left in the current month: SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "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) FROM EMP

    EMPNO ENAME      HIREDATE  REVIEW_DA

---

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

     7839 KING       17-NOV-81 17-MAY-82
     7566 JONES      02-APR-81 02-OCT-81
     7788 SCOTT      09-DEC-82 09-JUN-83
     7876 ADAMS      12-JAN-83 12-JUL-83
     7902 FORD       03-DEC-81 03-JUN-82
     7369 SMITH      17-DEC-80 17-JUN-81
     7698 BLAKE      01-MAY-81 01-NOV-81
     7499 ALLEN      20-FEB-81 20-AUG-81
     7521 WARD       22-FEB-81 22-AUG-81
     7654 MARTIN     28-SEP-81 28-MAR-82
     7844 TURNER     08-SEP-81 08-MAR-82
     7900 JAMES      03-DEC-81 03-JUN-82
     7782 CLARK      09-JUN-81 09-DEC-81
     7934 MILLER     23-JAN-82 23-JUL-82
     7955 WILSON     22-APR-88 22-OCT-88
     9000 GARVEY     23-JUL-08 23-JAN-09

16 rows selected.  List each employee's name and month of hire only: SELECT ENAME, TO_CHAR(HIREDATE,'MON') AS MONTH FROM EMP; 1.4 Inserting Date Values When you enter a value into a date field, it must be in the standard format. 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/2008',’DD/MM/YYYY’), 3400, 0, 20); COMMIT; 1.5 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

ENAME HIREDATE

---

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

FOX 23RD JULY 2008