Skip to main content

Date Function in SQL

Date function

— The Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, and seconds.
–-The default date display format is DD-MON-RR

SYSDATE

SYSDATE is a function that returns:
–Date
–Time

SELECT sysdate FROM dual
sysdate

ARITHMETIC WITH DATES

  • Add or subtract a number to or from a date for a resultant date value.
  • Subtract two dates to find the number of days between those dates.
  • Add hours to a date by dividing the number of hours by 24.

QUERY: Display employees name with number of months they completed in company.

SELECT last_name, (SYSDATE-hire_date)/30 AS Months
FROM employees
WHERE department_id = 90;

Date-Manipulation Functions

MONTHS_BETWEEN : IT is used for finding months between two dates.
ADD_MONTHS :It is used to add the months for a given dates.
NEXT_DAY :It is used for next date for given day.
LAST_DAY :It Is used for finding the last date for given month.

SELECT months_between(sysdate,'28-feb-1992') FROM dual;
Result value:
months-between
SELECT ADD_MONTHS(sysdate,4) FROM dual;
Result value:
add-months
SELECT NEXT_DAY(sysdate,'Friday') "NEXT DAY" FROM DUAL
Result value:
next-day
SELECT last_day('1-11-2016') FROM dual;
Result value:
last-day