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, some examples are: Format Display blank (without TO_CHAR) 25-DEC-07 MM/DD/YY 12/25/07 DD.MM.YYYY 25.12.2007 Month DD, YYYY December 25, 2007 DY DD MON YY SUN 25 DEC 07 Day Mon DD Sunday Dec 25 Day Month DD, YYYY Sunday December 25, 2007 DY DD Month YYYY SUN 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
