Date Functions Example

Date Functions Example

1. SYSDATE

The Sysdate function returns the current oracle database server date and time.

SELECT sysdate FROM DUAL;

2. Arithmetic with Dates

You can add or subtract the number of days or hours to the dates. You can also subtract the dates

SELECT sysdate+2 "add_days" FROM DUAL;

SELECT sysdate-3 "sub_days" FROM DUAL;

SELECT sysdate+3/24 "add_hours" FROM DUAL;

SELECT sysdate-2/24 "sub_hours" FROM DUAL;

SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns number of days between the two dates.

3. MONTHS_BETWEEN

The Months_Between function returns the number of months between the two given dates.

Syntax: months_between(date1,date2)

SELECT months_between(sysdate,hire_date) FROM EMPLOYEES:

SELECT months_between('01-JUL-2000', '23-JAN-2000') FROM DUAL;

4. ADD_MONTHS

The Add_Months is used to add or subtract the number of calendar months to the given date.

Syntax: add_months(date,n)

SELECT add_months(sysdate,3) FROM DUAL;

SELECT add_months(sysdate,-3) FROM DUAL;

SELECT add_months('01-JUL-2000', 3) FROM DUAL;

5. NEXT_DAY

The Next_Day function finds the date of the next specified day of the week. The syntax is

NEXT_DAY(date,'char')

The char can be a character string or a number representing the day.

SELECT next_day(sysdate,'FRIDAY') FROM DUAL;

SELECT next_day(sysdate,5) FROM DUAL;

SELECT next_day('01-JUL-2000', 'FRIDAY') FROM DUAL;

6. LAST_DAY

The Last_Day function returns the last day of the month.

SELECT last_day(sysdate) FROM DUAL;

SELECT last_day('01-JUL-2000') FROM DUAL;

7. ROUND

The Round function returns the date rounded to the specified format. The Syntax is
Round(date [,'fmt'])

SELECT round(sysdate,'MONTH') FROM DUAL;

SELECT round(sysdate,'YEAR') FROM DUAL;

SELECT round('30-OCT-85','YEAR') FROM DUAL;

8. TRUNC

The Trunc function returns the date truncated to the specified format. The Syntax is
Trunc(date [,'fmt'])

SELECT trunc(sysdate,'MONTH') FROM DUAL;

SELECT trunc(sysdate,'YEAR') FROM DUAL;

SELECT trunc('01-MAR-85','YEAR') FROM DUAL;