Oracle Date function

Select Ename,hiredate,sysdate from Scott.emp; can find out the date of employee's entry to present
Select Ename,sysdate-hiredate from Scott.emp; See how many days the employee has been in service;
Select Ename,months_between (sysdate,hiredate) from scott.emp how many months
Select Add_months (sysdate,6) from Scott.emp system time plus 6 months;

Number_tochar:
Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
Select Ename,to_char (Sal, ' l99,999.99 ') from dual;
Select Ename,to_char (Sal, ' $00,000.00 ') from dual;
9: Represents a number
0: Force display of a 0
$: Place a floating-point dollar sign
L: Use floating-point local currency symbol
Date_to_char:
Char_to_date:
Select To_date (' 1990-09-05 ', ' YYYY-MM-DD ') from dual;
Char_to_number:
Select To_number (' $00,800.00 ', ' l00,000.00 ') from dual; 800
---------
Select
To_char (sysdate, ' yyyy ') cyrr_year
To_char (To_date (' The ', ' yy '), ' YYYY ') yy07,
To_char (' to_date ', ' yy '), ' YYYY ') yy97,
To_char (To_date (' ', ' RR '), ' YYYY ') rr07,
To_char (' to_date ', ' RR, ' YYYY ') rr97
from dual;
------------
In fact, the following three calculation modes are available for dates:
Date + number = Date (date after several days)
Select Sysdate +10 from dual;
Date-Number = date (date prior to several days)
Select sysdate-10 from dual;
Date-date = number (number of days in two-day period)
Calculate the number of days of employment for each employee to date.
Select Ename,hiredate, sysdate-hiredate form scott.emp
Calculates the sum of the number of months experienced by two dates.
Syntax: Digital months_between (date 1, date 2)

The Months_between function returns the number of months between two dates.

1: Example: Calculates the total number of years that each employee has employed to date.
Select Ename,hiredate,
Trunc (Months_between (sysdate,hiredate)/12) years from Scott.emp
2: Increase the month date after a number of months;
Example: Testing the Add_months function
Select Add_months (sysdate,4) from dual;
3: The calculation is also less than 1 years full employment of 34 years of employment date.
SELECT * FROM Scott.emp
where Trunc (Months_between (sysdate,hiredate)/12) =34;
4: Calculates the last day of the month on which the specified date is located
Select Last_day (sysdate) from dual;
5: Find out the employee information for all employees who have been hired on the second day of the month of employment
The date of employment for each employee is different, and the second day of the month on which each employment date is located is different.
Select Ename,hiredate,last_day (HireDate), Last_day (HireDate)-2 from Scott.emp;
6: Calculate Next Tuesday
Select Next_day (sysdate, ' Tuesday ') from dual;
7: Calculate the year of employment employed in the Scott.emp table to date:
Select Empno,ename,hiredate, Trunc (Months_between (sysdate,hiredate)/12) year from Scott.emp
8: Comprehensive analysis: Asked to find out the employee's number, name, employment date, and the number of years each employee has been employed to date, the number of days of the month.
Assume that the download date is: 2016-03-08.
Select Empno,ename,hiredate,
Trunc (Months_between (sysdate,hiredate)/12) gear,
Trunc (mod (Months_between (sysdate,hiredate,)/12)) months,
Trunc (Sysdate-add_months (Hiredate,months_between (sysdate,hiredate)) Day
From Scott.emp;

Oracle Date function