Date Functions:
Oracle stores dates in an internal numeric format:
century year month day hours minutes and seconds
Default display format is DD-MON-YYYY
Oracle stores the year as 1996 & 2002 and not 96 & 00
1. SYSDATE() :
It is a function that returns:
Date.
select sysdate from dual;
2. Calculations on dates:
date + number Date ..adds a no of days to a date
date - number Date ..subtracts a no of days from a date.
date - date No of days ..subtracts one date from another.
date + number/24 Date ..adds a number of hours to a date.
-select dob+1 from one;
-select dob-1 from one;
-select (dob1-dob) from one;
- select dob1-dob from one
no of days between dob1 & dob.
- select (dob1-dob)/7 from one
no of weeks between dob1 & dob.
- select (dob1-dob)/365 from one.
no of years between dob1 & dob.
select first_name hire_date sysdate
'since ' ||round((sysdate-hire_date)/365) ||' years' from employees
3. Date Functions:
-select months_between(dob1 dob) from one
-select dob add_months(dob 3) from one.
-select next_day('20-mar-2003' 1) from one OR
..select next_day('20-mar-2003' 'sun') from one
..returns the next sunday after the specified date.
-select last_day(dob) from one
..returns the last day of the month of the specified date.
-select dob1 round(dob1 'month') from one
..rounds the date to the next month if it is > 15
..rounds the date to the previous month if it is < 15.
e.g. select hire_date round(hire_date 'year') from employees
select hire_date round(hire_date 'day') from employees
..rounds the date to the nearest sunday.
select hire_date round(hire_date 'month') from employees
e.g.
select hire_date add_months(hire_date 3)
(hire_date+90) from employees
