对比mysql学习oracle函数(三)：oracle单行函数—日期函数
原创
©著作权归作者所有：来自51CTO博客作者黄至尊qwe的原创作品，请联系作者获取转载授权，否则将追究法律责任
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字符集使用如下网址：
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
接着，我们在利用上述例子，进行时间筛选，效果如下。
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;
效果如下：
