Date Functions

Sysdate:
Ø  It is date function that returns current date and time.
Ø  Sysdate is generally selected upon a dummy table.
Ex:
  • Select sysdate from dual;
Date Arithmetic:
Ø  As database stores dates as numbers, arithmetic operations can be implemented.
Ø  Number constants can be added or subtracted upon dates.
Ø  The operations that can be applied are:
  • Date + number à Returns date (adds number of days to a date)
  • Date – number à Returns date (subtracts number of days to a date)
  • Date – Date à Returns number of days. (Subtracts one date from another date)
  • Date + number/24 à Returns Date (adds number of hours to a date)
Ex:
  • Select sysdate, sysdate+3 from dual;
  • Select sysdate, sysdate-3, sysdate + 72/24 from dual;
  • Select ename, hiredate, hiredate+3 from emp;
  • Select ename, hiredate, sysdate – hiredate from emp;
  • Select ename, (sysdate-hiredate)/7 weeks from emp;
Add_Months Function:
Syntax: add_months(D,n)
Ø  It returns the date ‘D’ plus or minus ‘n’ months
Ø  The argument ‘n’ can be any positive or negative integer.
Ex:
Ø  Select sysdate, add_months(sysdate,2) from dual;
Ø  Select sysdate, add_months(sysdate,-2) from dual;
Ø  Select sal, hiredate, add_months(hiredate,2) from emp;
Months_between Function:
Syntax: months_between (D1, D2)
Ø  It returns number of months between dates ‘D1’ and ‘D2’.
Ø  If D1 is later than D2 the result is positive, else negative.
Ø  If D1 and D2 are either the same days of the months or both last days of the months, the result is always an integer.
Ex:
  • Select ename, hiredate, sysdate, months_between(sysdate, hiredate) from emp;
  • Select empno, hiredate, months_between(sysdate,hiredate) from emp where months_between(sysdate,hiredate) < 200;
Next_day Function:
Syntax: next_day(d,char)
Ø  It returns the date of the first week day named by char, that is later than the date‘d’.
Ø  The char must be a day of the week in the sessions date language.
Ø  The day of the week can be full name or the abbreviation.
Ex:
  • Select sysdate, next_day(sysdate,’wed’) from dual;
  • Select sal, hiredate, next_day(hiredate, ’Monday’) from emp;
Last_day Function:
Syntax: last_day(D)
Ø  It returns the date of the last day of the month that contains ‘D’.
Ø  Mostly used to determine how many days are left in the current month.
Ex:
  • Select sysdate, last_day(sysdate) lastday from dual;
  • Select last_day(sysdate) last, sysdate, last_day(sysdate)-sysdate daysleft from dual;
Rounding of Dates:
Syntax: round(date, ’format’)
Ø  Returns date rounded to the unit specified by the format.
Ø  If format is omitted, date is rounded to the nearest day.
Ex:
  • Select sysdate, round(sysdate, ’DAY’) from dual;
  • Select sysdate, round(sysdate, ’MONTH’) from dual;
  • Select sysdate, round(sysdate,’YEAR’) from dual;
Truncating Dates:
Syntax: trunk(date, ‘format’)
Ø  Date is truncated to the nearest date with the time portion of the day truncated to the specified unit.
Ø  If format is omitted date is truncated to the nearest day.
Ex:

  • Select sysdate, trunc(sysdate, ‘DAY’) from dual;
  • Select sysdate, trunc(sysdate, ‘MONTH’) from dual;
  • Select sysdate, trunc(sysdate, ’YEAR’) from dual;