oracle单行函数之日期函数

举报
数据分析与统计学之美 发表于 2021/09/29 10:54:11 2021/09/29
【摘要】 Oracle第三课

1、单行函数分类:一行记录,返回一行结果;

在这里插入图片描述

1)日期函数介绍

在这里插入图片描述

  • 关于上图知识点,我们只关注一个知识点,Oracle中,默认显示的时间格式是“日-月-年”。
  • 当oracle服务端的字符集是simplified chinese_china.al32utf8simplified 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;

效果如下:
在这里插入图片描述

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200