How To Use Date Functions in Oracle
Date Functions
All date functions returns a value of date data type except months_between, which returns a numeric value.
- SYSDATE
- ADD_MONTHS(d, n)
- ROUND(d, [format])
- TRUNC(d, [format])
- MONTHS_BETWEEN(d1, d2)
- LAST_DAY(d)
- NEXT_DAY(date, day)
- TO_CHAR(d, f)
- TO_DATE(char, ‘f’)
SYSDATE
Example
- Select SYSDATE
- from dual;
ADD_MONTH(d, n)
This functions returns a date. It adds or subtracts month to or from a date.
Example
- Select Emp_Hiredate, ADD_MONTHS(Emp_hiredate, 4),
- ADD_MONTHS(Emp_hiredate, -4) from employee
- where Dept_no= 102;
ROUND(d, [format])
This function round off the dates d to the unit, specified by format.
Example
- select ROUND(TO_DATE('17-june-07'), 'MM')"Nearest Month"
- from dual;
TRUNC(d, [format])
This functions returns the date d, truncated to the unit, specified by format.
Example
- Select TRUNC (TO_DATE('17-june-07 12:11:01','DD-MON-YY HH24:MI:SS'), 'year')
- "first day"
- from dual;
MONTHS_BETWEEN(d1, d2)
This function returns the number of months between two dates, d1 and d2.
Example
- select MONTHS_BETWEEN('17-may-06', '01-sep-08'),
- MONTHS_BETWEEN('18-march-06','17-june-07')
- from dual;
LAST_DAY(d)
The function returns the date of the last day of the specified month.
Example
- Select SYSDATE, LAST_DAY(SYSDATE)
- From dual;
NEXT_DAY(date, day)
This function returns the date of the next specified day of the week after the date.
Example
- Select SYSDATE, NEXT_DAY(SYSDATE, ‘MONDAY’)
- from dual;
TO_CHAR(d, f)
This function converts the date ‘d’ to character format ‘f’.
Example
- Select SYSDATE, TO_CHAR(SYSDATE, ‘DAY’)
- From Dual;
TO_DATE(char, ‘f’)
This functions converts the character string representing date into a date format, according to specified ‘f’ format.
Example
- Select TO_CHAR(TO_DATE(’17-may-06’), ‘MM’)
- from dual;
Summary