Oracle Day 1

日期函数

add_months()

Select 
Sysdate, Add_Months(Sysdate, 3)三个月之后的日期, Add_Months(Sysdate,-3) 三个月之前的日期
from dual;

查询所有雇员入职三个月之后的日期

 Select Empno,Ename, Hiredate, Add_Months(Hiredate,3)
From emp;

使用next_day()函数

Select Sysdate,
Next_Day(sysdate,'星期二') 
from dual;

使用 Last_day()求得指定日期所在月的最后一天。用以下

Select Empno,Ename, Job, Hiredate,Last_Day(Hiredate)
From Emp
where last_day(hiredate)-2 = hiredate;

使用**months_between()，求得两个日期之间的月数

Select
Empno,
Ename,
Hiredate,
Trunc(Months_Between(Sysdate,Hiredate)),
trunc(months_between(sysdate,hiredate)/12)
from emp;

使用日期函数查询所有雇员在职的年数-月数-天数

Select
Empno,
Ename,
Hiredate,
Trunc(Months_Between(Sysdate,Hiredate)/12) 雇佣总年份,
Trunc(Mod(Months_Between(Sysdate,Hiredate),12)) 雇佣总月份,
trunc(sysdate - add_months(hiredate,months_between(sysdate,hiredate))) 雇佣总天数

from emp;

可以用extract()简化上述代码

Select 
Extract( Year From Date '2018-09-04') Years,
Extract( Month From date '2018-09-04') Months,
extract(day from date '2018-09-04')days
from dual;

或者如下

Select 
Extract( Year From sysdate) Years,
Extract( Month From  sysdate) Months,
extract(day from sysdate)days
from dual;

又或者如下：

Select 
Extract( Year From systimestamp) Years,
Extract( Month From  Systimestamp) Months,
Extract(Day From Systimestamp)Days,
Extract(Hour From Systimestamp)Hourss,
Extract(Minute From Systimestamp)Minutes,
extract(Second from systimestamp)Seconds
from dual;

或者查找 时间间隔

Select 
Extract (Day From Datetime_One - Datetime_Two) Days,
Extract (Hour From Datetime_One - Datetime_Two)Hours,
Extract (Minute From Datetime_One - Datetime_Two) Minutes,
extract( second from Datetime_One - Datetime_Two) seconds
From (
  Select 
  To_Timestamp('2018-02-5 12:45:48','yyyy-mm-dd hh24:mi:ss') Datetime_One,
  To_Timestamp('2017-02-8 12:8:38','yyyy-mm-dd hh24:mi:ss')Datetime_Two
  from dual
);