Date function (Learning Note)

--******************** Date function
--Get current system time
SELECT sysdate from dual;
--Query 3 days from today, and 3 days before the date
SELECT Sysdate Today,
SYSDATE-3 three days ago,
Sysdate+3 three days later
from dual;
--Find out the number of entry days for each employee into today, and the number of days each employee entered the job 10 days ago
SELECT e.hiredate Date of entry,
Sysdate Today,
Sysdate-e.hiredate into today's days,
Sysdate-10-e.hiredate 10 days before the entry days
From EMP E;

--use trunc to intercept days
SELECT e.hiredate Date of entry,
Sysdate Today,
Trunc (Sysdate-e.hiredate) The number of days of entry to today,
Trunc (sysdate-10-e.hiredate) 10 days before the entry days
From EMP E;
--months_between (date 1, date 2) Find the number of months between 2 dates
--add_months (date, number) to add or subtract a specified number of months on a specified date, a number that can be positive or negative
--next_day (date, day of the week) find out the exact date of next week
--last_day (date) Date of the last day of the specified date
--extract (format from date) to exclude dates, or to calculate intervals of 2 dates

--add_months (date, number)
SELECT sysdate Current date,
Add_months (sysdate,3) Three months after the date,
Add_months (sysdate,-3) Three months after the date,
Add_months (sysdate,60) date after 60 months
from dual;
--Check the date of all employees in the 3 months after entry
SELECT e.empno,e.ename,e.hiredate,add_months (e.hiredate,3) Date of entry three months
From EMP E;
--next_day (date, day of the week)
SELECT sysdate Current date,
Next_day (sysdate, ' Friday ') next Friday,
Next_day (sysdate, ' Monday ') next Monday
from dual;
--last_day () Last day of one months date
SELECT Sysdate,last_day (sysdate) from dual;

--Check all employees who entered the job on the last day of the month on the date of entry-2
SELECT e.empno,e.ename,e.hiredate
From EMP E
WHERE E.hiredate=last_day (E.hiredate)-2;
--months_between
--Query Each employee's number, name, entry date, number of months and year of entry
--Number of months Months_between (sysdate,hiredate) years Months_between (sysdate,hiredate)/12

SELECT E.empno,e.ename,e.hiredate,
Trunc (Months_between (sysdate,e.hiredate)) Number of months of entry,
Trunc (Months_between (sysdate,e.hiredate)/12) Number of years of entry
From EMP E;
--Query Each employee's number, name, entry date, year of entry, month and number of days
--Total number of months, and 12 modulo is the number of months
--Daily Current date-(date of entry + number of months)
SELECT E.empno,e.ename,e.hiredate,
TRUNC (Months_between (sysdate,e.hiredate)/12) years,
TRUNC (MOD (Months_between (sysdate,e.hiredate), 12)) month,
TRUNC (Sysdate-add_months (E.hiredate,months_between (sysdate,e.hiredate))) Day
From EMP E;
--extract function
SELECT Sysdate,
EXTRACT (year from Sysdate),
EXTRACT (month from Sysdate),
EXTRACT (Day from Sysdate)
from dual;

--Remove the year, month, day, hour, minute and second from the timestamp
SELECT Systimestamp,
EXTRACT (year from Systimestamp),
EXTRACT (month from Systimestamp),
EXTRACT (Day from Systimestamp),
EXTRACT (HOUR from Systimestamp),
EXTRACT (MINUTE from Systimestamp),
EXTRACT (SECOND from Systimestamp) sec
from dual;

Date function (Learning Note)