Oracle日期函数及求闰年方法

谁借莪１个温暖的怀抱￠ 2024-03-22 09:09 14阅读 0赞

**目录**

一、熟悉几种日期函数的用法

①sysdate

②add\_months(日期,数)

③months\_between(日期1,日期2)

④next\_day(日期,'星期几'/对应的数字)

⑤last\_day(日期) 月的最后一天

⑥round(日期,'格式')

⑦trunc(日期,'格式')

二、日期函数总结：

三、闰年查询

一、按照二月份天数为29的方式查找

二、判断2-28(31+28=59)后一天在二月份还是三月份

三、按照闰年判定方法：能被4整除，并且不能被100被整除，或者能被400整除

四、按照闰年全年天数为366天方式查找

--------------------

## 一、熟悉几种日期函数的用法 ##

首先清楚日期的运算规则

日期±数字=日期±天数

日期－日期=相差的天数

### ①sysdate ###

select sysdate from dual；

比如：

\--查询当前时间距离1995-10-27有多久

select sysdate-date'1995-10-27' from dual；

\--查询1天后,一小时后,一分钟后,一秒后的日期

select sysdate+1,  --一天
           sysdate+1/24,  --一小时
           sysdate+1/24/60,  --一分钟
           sysdate+1/24/60/60  --一秒钟
    from dual;

\--查询2022年2月份的天数

select date'2022-3-1'-date'2022-2-1'
    from dual;

\--查询今天距离2030年元旦还有多少天

select date'2030-1-1'-sysdate
    from dual;

### ②add\_months(日期,数) ###

\--日期加数月\[数会裁断到整数,见运行结果\]

select add_months(sysdate,1) 下个月,
           add_months(sysdate,-1) 上个月,
           add_months(sysdate,12) 明年,
           add_months(sysdate,1.78)
    from dual;

![758616da80324fb7ac288efe06162538.png][]

比如：

\--查询明年的今天是星期几

select to_char(add_months(sysdate,12),'dy')
    from dual;

![282d5781d9aa47cea1c6e0ababd23622.png][]

### ③months\_between(日期1,日期2) ###

两个日期相差的月数，多出来的天数会除以31

oracle的算法是1个月=31天

select months_between(sysdate,date'2022-3-2')
    from dual;

比如：

\--查询2012-12-15距今多少年

select round(months_between(sysdate,date'2012-12-15')/12,2)||'年'
    from dual;

\--查询2018-6-6距今几个月零几天

\--小数部分乘31

select floor(months_between(sysdate,date'2018-6-6'))||'个月'||
           ceil(31*mod(months_between(sysdate,date'2018-6-6'),1))||'天' a
    from dual;

![40186d9c8f12444f93cc109b59c6b0bb.png][]

另一种方法求解：

select floor(months_between(sysdate,date'2018-6-6'))||'个月'||
           ceil(sysdate-add_months(date'2018-6-6',floor(months_between(sysdate,date'2018-6- 
           6'))))||'天' a
    from dual;

运行结果同上；

<table style="width:500px;">  日期算法  
 <tbody> 
  <tr> 
   <td>求两个日期相差的--</td> 
  </tr> 
  <tr> 
   <td> <p>年数 months_between()/12</p> <p>月数 months_between()</p> <p>天数 日期相减</p> <p>小时数 日期相减*24</p> <p>分钟数 日期相减*24*60</p> <p>秒数 日期相减*24*60*60</p> </td> 
  </tr> 
  <tr> 
   <td>日期加减--</td> 
  </tr> 
  <tr> 
   <td> <p>年 add_months(日期,年数*12)</p> <p>月 add_months(日期,月数)</p> <p>天 日期±天数</p> <p>小时 日期±小时数/24</p> <p>分钟 日期±分钟数/24/60</p> <p>秒 日期±秒数/24/60/60</p> </td> 
  </tr> 
 </tbody> 
</table>

### ④next\_day(日期,'星期几'/对应的数字) ###

\--下个星期几

select next_day(sysdate,'星期二'),next_day(sysdate,1)
    from dual;

比如：

\--下周的星期五

select next_day(next_day(sysdate,6),6)
    from dual;

\--求2020年的第一个星期五,2019年11月的第一个星期五

select next_day(date'2019-12-31',6),next_day(date'2019-10-31',6)
    from dual;

\--求本周日是哪一天(为了解决今天是周日,本周日求不到当日的问题,在当前日期减1即可)

select next_day(sysdate-1,1)
    from dual;

\--求下周三是多少号（用上面这个方法）

select next_day(next_day(sysdate-1,1),4)
    from dual;

\--求下周五是几月几号

select to_char(next_day(next_day(sysdate-1,1),6),'mm-dd')
    from dual;

### ⑤last\_day(日期) 月的最后一天 ###

\--（可以用来求2月的最后一天）

select last_day(sysdate),last_day(date'2022-2-1')
    from dual;

比如：

\--查询3个月后的月末是星期几

select to_char(last_day(add_months(sysdate,3)),'dy')
    from dual;

\--查询今天距离月末还有多少天

select last_day(sysdate)-sysdate
    from dual;

### ⑥round(日期,'格式') ###

\--四舍五入到日期之初（过半则入，不过则舍）

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td> <p>yyyy&nbsp; &nbsp; &nbsp;1-6月&nbsp; &nbsp;本年&nbsp; &nbsp; &nbsp; 7-12月&nbsp; &nbsp; &nbsp; 下一年 --到最近的年初</p> <p>mm&nbsp; &nbsp; &nbsp; 1-15日 本月&nbsp; &nbsp; &nbsp; 16-31日&nbsp; &nbsp; 下个月 --到最近的月初 （月份按照一个月30来计算）</p> <p>d/dy&nbsp; &nbsp; &nbsp;1-4(周日-周三) 本周&nbsp; &nbsp; &nbsp; 5-7(周四-周六) 下周 --到最近的周日 （周日是一周的第一天）</p> <p>dd&nbsp; &nbsp; &nbsp; &nbsp; 0-11时 今天&nbsp; &nbsp; &nbsp; &nbsp; 12-23时&nbsp; &nbsp;下一天--最近的一天之初</p> <p>hh&nbsp; &nbsp; &nbsp; &nbsp; 0-29分 本小时&nbsp; &nbsp; 30-59分&nbsp; &nbsp;下一个小时--最近的整时</p> <p>mi&nbsp; &nbsp; &nbsp; &nbsp; 0-30秒 这分钟&nbsp; &nbsp; 31-59秒&nbsp; &nbsp; 下分钟--最近的整分</p> <p>q&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;=当季第二个月的15日&nbsp; 本季&nbsp; &nbsp; &nbsp; &nbsp;&gt;=当季第二个月的16日 下个季度</p> <p>不能四舍五入到整秒</p> </td> 
  </tr> 
 </tbody> 
</table>

比如：

\--当前日期四舍五入到整年,整季,整月,整周,整天,整时,整分 分别哪个日期

SELECT ROUND(SYSDATE,'yyyy') 整年,
           ROUND(SYSDATE,'q')    整季,
           ROUND(SYSDATE,'mm')   整月,
           ROUND(SYSDATE,'d')    整周,
           ROUND(SYSDATE,'dd')   整天,
           ROUND(SYSDATE,'hh')   整时,
           ROUND(SYSDATE,'mi')   整分
         --,round(sysdate,'ss') --错误
    FROM dual;

![1b184abce78e49abaa7ae7a4fd632dce.png][]

### ⑦trunc(日期,'格式') ###

\--截断到日期之初

比如：

\--当前日期截断到整年,整季,整月,整周,整天,整时,整分 分别哪个日期

SELECT TRUNC(SYSDATE,'yyyy') 整年,
           TRUNC(SYSDATE,'q')    整季,
           TRUNC(SYSDATE,'mm')   整月,
           TRUNC(SYSDATE,'d')    整周,
           TRUNC(SYSDATE,'dd')   整天,
           TRUNC(SYSDATE,'hh')   整时,
           TRUNC(SYSDATE,'mi')   整分
        --,round(sysdate,'ss') 错误
    FROM dual;

![03ef17e0a9584291a78b1e3c801c229d.png][]

\--今年有多少天

select to_char(trunc(add_months(sysdate,12),'yyyy')-1,'ddd')
    from dual;
    
    select to_char(add_months(trunc(sysdate,'yyyy'),12)-1,'ddd')
    from dual;

\--本月有多少天

select to_char(last_day(sysdate),'dd')
    from dual;

## 二、日期函数总结： ##

<table style="width:500px;">  函数名(日期,'格式')对比  
 <tbody> 
  <tr> 
   <td></td> 
   <td>to_char</td> 
   <td>ROUND</td> 
   <td>TRUNC</td> 
  </tr> 
  <tr> 
   <td>yyyy</td> 
   <td>提取年</td> 
   <td>返回最近的年初</td> 
   <td>返回年初</td> 
  </tr> 
  <tr> 
   <td>q</td> 
   <td>提取季度</td> 
   <td>返回最近的季初</td> 
   <td>返回季初</td> 
  </tr> 
  <tr> 
   <td>mm</td> 
   <td>提取月</td> 
   <td>返回最近的月初</td> 
   <td>返回月初</td> 
  </tr> 
  <tr> 
   <td>dy</td> 
   <td>提取星期(汉字)</td> 
   <td>返回最近的周初</td> 
   <td>返回周初(周日)</td> 
  </tr> 
  <tr> 
   <td>d</td> 
   <td>提取星期(数字)</td> 
   <td>返回最近的周初</td> 
   <td>返回周初(周日)</td> 
  </tr> 
  <tr> 
   <td>dd</td> 
   <td>提取日</td> 
   <td>返回最近的天初</td> 
   <td>返回天初</td> 
  </tr> 
  <tr> 
   <td>hh/hh12</td> 
   <td>提取时(12制)</td> 
   <td>返回最近的时初</td> 
   <td>返回时初</td> 
  </tr> 
  <tr> 
   <td>hh24</td> 
   <td>提取时(24制)</td> 
   <td>返回最近的时初</td> 
   <td>返回时初</td> 
  </tr> 
  <tr> 
   <td>mi&nbsp;</td> 
   <td>提取分</td> 
   <td>返回最近的分初</td> 
   <td>返回分初</td> 
  </tr> 
  <tr> 
   <td>SS</td> 
   <td>提取秒</td> 
   <td>/*精度错误*/</td> 
   <td>/*精度错误*/</td> 
  </tr> 
 </tbody> 
</table>

<table style="width:500px;">  各函数返回类型  
 <tbody> 
  <tr> 
   <td>函数名</td> 
   <td>SYSDATE</td> 
   <td>add_months</td> 
   <td>months_between</td> 
   <td>next_day</td> 
   <td>last_day</td> 
   <td>round</td> 
   <td>trunc</td> 
  </tr> 
  <tr> 
   <td>返回类型</td> 
   <td>日期</td> 
   <td>日期</td> 
   <td>数值</td> 
   <td>日期</td> 
   <td>日期</td> 
   <td>日期</td> 
   <td>日期</td> 
  </tr> 
 </tbody> 
</table>

熟知这些日期函数的定义以及用法，接下来举个实例运用一下，加深理解和使用！！！

## 三、闰年查询 ##

**已知员工表emp,要求查询出所有在闰年入职的员工信息**

### 一、按照二月份天数为29的方式查找 ###

select *
    from emp
    where to_char(last_day(to_date(to_char(hiredate,'yyyy')||'-2-1','yyyy-mm-dd')),'dd')=29；
    
    select *
    from emp
    where to_char(to_date(to_char(hiredate,'yyyy')||'-3-1','yyyy-mm-dd')-1,'dd')=29；
    
    select *
    from emp
    where to_char(last_day(add_months(trunc(hiredate,'yyyy'),1)),'dd')=29；

### 二、判断2-28(31+28=59)后一天在二月份还是三月份 ###

select *
    from emp
    where to_char(trunc(hiredate,'yyyy')+59,'mm')=2;

### 三、按照闰年判定方法：能被4整除，并且不能被100被整除，或者能被400整除 ###

select *
    from emp
    where mod(to_char(hiredate,'yyyy'),4)=0 and
          mod(to_char(hiredate,'yyyy'),100)!=0 or
          mod(to_char(hiredate,'yyyy'),400)=0;

### 四、按照闰年全年天数为366天方式查找 ###

select *      
    from emp
    where trunc(add_months(hiredate,12),'yyyy')-trunc(hiredate,'yyyy')=366；
    
    select *
    from emp
    where to_char(trunc(add_months(hiredate,12),'yyyy')-1,'ddd')=366;
    
    select *
    from emp
    where to_char(to_date(to_char(hiredate,'yyyy')||'-12-31','yyyy-mm-dd'),'ddd')=366;

[758616da80324fb7ac288efe06162538.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/73213686a9d748dd847534c08a8432ab.png
[282d5781d9aa47cea1c6e0ababd23622.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/7a9f68b1ad0f49a6be5e85f44eed6e93.png
[40186d9c8f12444f93cc109b59c6b0bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/dd2c52fb304a4ca4ab6b62cff430a81c.png
[1b184abce78e49abaa7ae7a4fd632dce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/57aaa07ee6ac40c18db940a67f6f6eea.png
[03ef17e0a9584291a78b1e3c801c229d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/07075bf4a89343c9852e4a358d47c779.png