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;