oracle单行函数之日期函数

1、单行函数分类：一行记录，返回一行结果；

1）日期函数介绍

• 关于上图知识点，我们只关注一个知识点，Oracle中，默认显示的时间格式是“日-月-年”。
• 当oracle服务端的字符集是simplified chinese_china.al32utf8或simplified chinese_china.zhs16gbk的时候，那么时间格式缺省显示类似于：2003-1月-28。
• 当oracle服务端的字符集是us7ascii字符集的，缺省的时间格式显示为：28-Jan-2003。
• 当然，SIMPLIFIEDCHINESE_CHINA.AL32UTF8是最好的，可以兼容多国文字。当然oracle服务端字符集的修改，也不是由我们来决定的，这是DBA的事儿。
• 查看oracle字符集使用如下网址：
  https://blog.csdn.net/Lizi_TT/article/details/89917565

SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8

  我的数据库服务端的字符集，如上所示，所以时间格式缺省显示类似于：2003-1月-28。因此在进行数据筛选的时候，也必须采用同样的格式，进行时间筛选，否则会报错。

SQL> select empno,ename,hiredate from emp where hiredate='17-12-1980';
 
select empno,ename,hiredate from emp where hiredate='17-12-1980'
 
ORA-01843: 无效的月份
 
SQL> select empno,ename,hiredate from emp where hiredate='17-12月-1980';
 
EMPNO ENAME      HIREDATE
----- ---------- -----------
 7369 SMITH      1980/12/17

  假如你不想使用上述时间格式进行时间筛选，可以使用如下代码：改变当前会话窗口的默认时间显示格式。

SQL> alter session set nls_date_format="YYYY-MM-DD HH:MI:SS";
 
Session altered

  接着，我们在利用上述例子，进行时间筛选，效果如下。

SQL> select empno,ename,hiredate from emp where hiredate='17-12月-1980';
 
select empno,ename,hiredate from emp where hiredate='17-12月-1980'
 
ORA-01861: 文字与格式字符串不匹配
 
SQL> select empno,ename,hiredate from emp where hiredate='1980-12-17';
 
EMPNO ENAME      HIREDATE
----- ---------- -----------
 7369 SMITH      1980/12/17

注意：上述代码执行后，仅对当前会话窗口有效，也就是说，当关闭了该窗口后，时间显示格式又会恢复默认状态。

2）日期函数运算

• 日期类型的数据，仅仅支持“+”或者“-”算术运算符。
• 日期类型的数据，使用“+”算术运算符只能是加减某个数值(该数值会被按照天数来计算)，而不能是某个日期。也就是说：日期+日期没有什么意义。
• 日期类型的数据，使用“-”算术运算符，既可以加减某个数值(该数值会被按照天数来计算)，又可以进行日期相减，“日期-日期=天数”。

SQL> select empno,ename,hiredate,(hiredate+90) from emp where hiredate='1980-12-17';
 
EMPNO ENAME      HIREDATE    (HIREDATE+90)
----- ---------- ----------- -------------
 7369 SMITH      1980/12/17  1981/3/17
 
SQL> select empno,ename,hiredate,(hiredate+to_date('1980-12-15')) from emp where hiredate='1980-12-17';
 
select empno,ename,hiredate,(hiredate+to_date('1980-12-15')) from emp where hiredate='1980-12-17'
 
ORA-00975: 不允许日期 + 日期    -- 可以看出这里报错了：不允许日期 + 日期
 
SQL> select empno,ename,hiredate,(hiredate-90) from emp where hiredate='1980-12-17';
 
EMPNO ENAME      HIREDATE    (HIREDATE-90)
----- ---------- ----------- -------------
 7369 SMITH      1980/12/17  1980/9/18
 
SQL> select empno,ename,hiredate,(hiredate-to_date('1980-12-15')) from emp where hiredate='1980-12-17';
 
EMPNO ENAME      HIREDATE    (HIREDATE-TO_DATE('1980-12-15'
----- ---------- ----------- ------------------------------
 7369 SMITH      1980/12/17                               2

3）常用的日期函数

  上面我们已经使用了一个转换函数to_date()，该函数可以将字符串转换为日期函数，下面的函数讲解基于这个函数进行一一说明。
  注意：想要精确计算日期，最好使用如下这些函数进行时间的加减，具体点说就是：使用函数进行时间加减，走的是日历上面的时间，而使用是个时间加减一个数字得到的日期并不准确，因为有时候一个月28或29天，有时候一个月30或31天。

① months_between(date1,date2)：计算date1-date2相差多少个月。

SQL> select months_between(to_date('2019-12-1'),to_date('2019-10-1')) from dual;
 
MONTHS_BETWEEN(TO_DATE('2019-1
------------------------------
                             2
 
SQL> select months_between(to_date('2019-12-2'),to_date('2019-10-1')) from dual;
 
MONTHS_BETWEEN(TO_DATE('2019-1
------------------------------
              2.03225806451613
 
SQL> select floor(months_between(to_date('2019-12-2'),to_date('2019-10-1'))) from dual;
 
FLOOR(MONTHS_BETWEEN(TO_DATE('
------------------------------
                             2

• 注意1：在使用该函数的时候，最好用当前时间减去过去时间，这样计算出来的值，是一个正数。
• 注意2：假如计算出来的月数，是一个小数，可以配合floor地板函数使用。以上述代码中的“2.03225806451613”个月来进行说明，该值大于1个月，但是小于3个月，因此我们使用floor函数，表示取小于等于“2.03225806451613”的最大整数，也就是2。

② add_months(date,num)：在一个日期date上，加减某个num数字(该数字表示的是月份)，得到一个新的日期。

• 从上面的显示结果可以看出：使用函数进行日期的加减，显得更为准确。因为3个月并不一定就是90天，使用函数会严格按照日历给你算当前日期下面的3个月，究竟有多少天。

③ next_day(date,num)：计算该日期，下一个星期几的具体日期。

• num=1表示计算下一个星期一的日期；
• num=2表示计算下一个星期二的日期；
• 以此类推：
• num=7表示计算下一个星期天的日期。
• 注意：西方时间中，我们的周日是他们的周一，因此当我们计算出某个日期后，需要加1，才是我们想要的日期。

SQL> select next_day(sysdate,1)+1 from dual;
 
NEXT_DAY(SYSDATE,1)+1
---------------------
2019/12/23 15:58:57
-- 使用extract函数，可以提取日期中的日、月、年，具体用法会在下面说明。
SQL> select extract(year from (next_day(sysdate,1)+1)) from dual;
 
EXTRACT(YEARFROM(NEXT_DAY(SYSD
------------------------------
                          2019
 
SQL> select extract(month from (next_day(sysdate,1)+1)) from dual;
 
EXTRACT(MONTHFROM(NEXT_DAY(SYS
------------------------------
                            12
 
SQL> select extract(day from (next_day(sysdate,1)+1)) from dual;
 
EXTRACT(DAYFROM(NEXT_DAY(SYSDA
------------------------------
                            23

④ last_day()：返回当前月份最后一天的日期。

SQL> select last_day(sysdate) from dual;
 
LAST_DAY(SYSDATE)
-----------------
2019/12/31 15:40:
 
SQL> select last_day(to_date('2019-2-1')) from dual;
 
LAST_DAY(TO_DATE('2019-2-1'))
-----------------------------
2019/2/28

⑤ round()：记住如下用法就行。

SQL> select sysdate,
  2  round(sysdate),           -- 最接近0点的日期；
  3  round(sysdate,'day'),     -- 最近的星期日的日期；
  4  round(sysdate,'month'),   -- 最近的月初的日期；
  5  round(sysdate,'q'),       -- 最近的季初的日期；
  6  round(sysdate,'year')     -- 最近的年初的日期；
  7  from dual;
 
SYSDATE     ROUND(SYSDATE) ROUND(SYSDATE,'DAY') ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'Q') ROUND(SYSDATE,'YEAR')
----------- -------------- -------------------- ---------------------- ------------------ ------------------------------
2019/12/16  2019/12/17     2019/12/15           2020/1/1               2020/1/1           2020/1/1

⑥ trunc()：记住如下用法就行。

SQL> select sysdate,
  2  trunc(sysdate),           -- 返回当前日期；
  3  trunc(sysdate,'day'),     -- 返回当前星期的第一天；
  4  trunc(sysdate,'month'),   -- 返回当月的第一天；
  5  trunc(sysdate,'q'),       -- 返回当前季度的第一天；
  6  trunc(sysdate,'year')     -- 返回当前年份的第一天；
  7  from dual;
 
SYSDATE     TRUNC(SYSDATE) TRUNC(SYSDATE,'DAY') TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'Q') TRUNC(SYSDATE,'YEAR')
----------- -------------- -------------------- ---------------------- ------------------ ------------------------------
2019/12/16  2019/12/16     2019/12/15           2019/12/1              2019/10/1          2019/1/1

⑦ 补充一个处理日期：提取日期中的日月年。

• 与mysql中函数的写法不太一样，Oracle中没有提取日期中年、月、日的year()、month()、day()等函数。

SQL> select hiredate,extract (year from hiredate)"年", 
	 extract (month from hiredate)"月",
	 extract (day from hiredate)"日"
 	 from emp;

效果如下：