Date Functions of Oracle

 By learning date functions you can search, update and modify the date information of the employees easily. Here the Date Functions are described in details.

   Use of SYSDATE  Functions.

  In oracle Database Management, Date Functions are very important. By these functions you can get date data in an arranged order as you like.

   Sysdate means current date or system date. The sysdate functions returns the current database server date and time. You can use this function in SELECT query.
SQL Statement:

select (sysdate-hire_date)/7 "Weeks Employed" from employees where department_id=80;

By the above  'select' query you can know how many weeks an employee have worked in a company. Here 'where clause' is added. This query will return date data of those employees who have the employee_id=80. It is noted that after 'select' you will use 'space'. Apply this SQL Statement and move forward. See the result below.

Image of Weeks Employed made by SQL Statement

This date data doesn't look good.

So If you use 'round' function, your data will look nice.

SQL Statement:

select round((sysdate-hire_date)/7,4)"Weeks Employed" from employees where department_id=80;

Result: See the below image.

Image of Weeks Employed looking gorgeous after performing Round function 

If you need the date data  of the employees how many months or years they have worked in a company, you can get this kind of data by applying the following SQL statement:

select round((sysdate-hire_date)/365,4)"Years Employed" from employees where department_id=80;


The above sql statement is the data of years how many years the employees have worked.

In the same way you can get months data by using '30' instead of '365' in the sql statement. That is why, you can change this red colored values in this sql statement. Along with this change, the other values will change.
SQL Statement:
select round((sysdate-hire_date)/365,4)"Years Employed" from employees where department_id=80;

You can change the red options according to your need or liking.

Date Arithmetic: You can use arithmetic operators to subtract dates, add some numeric values with dates etc. In the previous query, you have subtracted hire_date from sysdate. The result is the number of days or months or years. But the following table is discussing the outcomes of different arithmetic operators. These operators can be applied on date type values.

Operation	Outcome
data+     number	date value. adds the number of days with the given date                                                          values. As for example sql statement: select last_name, hire_date+15 from employees;
Date - number	date value, subtracts the number of the days from the                                                             given date values.  Please see the sql statement:                      select last_name, hire_date-15 from employees;
date - date	a numeric value specifying the number of days between these two dates.  The sql statement:  select last_name, sysdate - hire_date from employees;

This above result doesn't look attractive. So you can use the 'round' operator and alias. Use this sql statement for making the query good. The sql statement:


select last_name,round((sysdate - hire_date),2)"Total Days" from employees;



The outcome of this sql statement will look beautiful.

 But Date + date : outcome is invalid. You can't get any result.

 Date Manipulation Functions: 

Operation	Outcome
months_between (date1, date2)	Number of months between date1 and date2. The qsl statement: select last_name, months_between(sysdate, '01-feb-1995') from employees;
Add_months(date1, date2)	add 'n' months with the date. Result is another date. Follow this sql statement: select last_name, add_months(sysdate, 5) from employees;
date - date	a numeric value specifying the number of days between these two dates.  The sql statement:  select last_name, sysdate - hire_date from employees;