Oracle日期操作函数不完全简介（上篇）

SQL干货分享 2021-08-13

722

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家好，今天小刘将和诸位明公分享一下 Oracle 中有关日期类型的操作函数的简单使用

之所以会有今天的这个分享，完全是由于一位神仙触动了我

事情是这样的，小刘今年接手了一个项目，其中涉及到需要二次开发前团队留下的一些功能，于是小刘就在一个视图中发现了下面这样一段判断日期字段是否为周日的天堂代码

decode(to_char(request_date, 'DAY')
      ,'星期天'
      ,request_date - 1
      ,'星期日'
      ,request_date - 1
      ,'SUNDAY'
      ,request_date - 1
      ,'7'
      ,request_date - 1
      ,request_date)

可见这位仙人不仅考虑到可能发生的中英语言环境的差异，甚至考虑到了中文中方言的区别，还有数据库偷懒只返回了一个序号的情况

小刘想来想去，反正怎么水都是水，不如水些真情实感的，于是就有了今天的不完全总结版本，请各位大佬帮忙看看有没有错误和遗漏

TO_DATE 和 TO_CHAR

这两大函数想必无需小刘赘述它们的功能，小刘在这里着重介绍一下常见的日期模板字符串的内容

CC	世纪数
D	数字表示的周几（1 = 日，2 = 一，7 = 六）
DAY	文字格式的周几
DD	本月的第几天，也就是几号
DDD	本年的第几天
HH HH12	十二小时制的时
HH24	廿四小时制的时
IW	符合ISO 8601标准的全年总第几周
MI	分
MM	数字表示的月份
MONTH	文字格式的月份
AM	上午
PM	下午
Q	季度（一到四月为第一季度）
SS	秒
SSSSS	从今天午夜算起到现在过了多少秒
W	本月总第几周（1到7号算第一周）
YYYY YYY YY Y	年份的最后4，3，2，1位数字

SQL> SELECT to_char(SYSDATE, 'yyyy"年"mm"月"dd"日" amhh:mi:ss') str_time
  2        ,to_char(SYSDATE, 'day') str_day
  3        ,to_char(SYSDATE, 'd') num_day
  4        ,to_char(SYSDATE, 'month') str_month
  5    FROM dual;
  
STR_TIME                         STR_DAY      NUM_DAY STR_MONTH
-------------------------------- ------------ ------- ---------
2020年09月14日 下午09:44:35      星期一       2       9月

观察上例我们发现，在日期转字符串时，AM 和 PM 其实都是在要求函数给出日期变量的上下午，而在字符串转日期时，AM 和 PM 实际发挥着说明十二小时制的小时数代表上午还是下午，尤其是 0~12 点，系统一般默认为二十四小时制，此时如果字符串中实际是十二小时制的下午时间，一定要指明 PM

另外 TO_CHAR 还有第三个可选参数，即 NLS 参数指示字符串，这里只简单说明一下 NLS_DATE_LANGUAGE 参数可以指示语言环境

SQL> SELECT to_char(SYSDATE, 'day', 'nls_date_language = japanese') jap_day
  2        ,to_char(SYSDATE, 'month', 'nls_date_language = american') ame_month
  3    FROM dual;


JAP_DAY      AME_MONTH
------------ ------------------------------------
月曜日       september

ADD_MONTHS 和 MONTHS_BETWEEN

众所周知，在 Oracle 中如果将一个日期类型的量直接加或减一个数字，实际上这里的数字的单位是天

从而 1/24 代表一小时，1/24/60 代表一分钟，以此类推

但是想要加减月份就没有通用公式了，因为月份的日数是不固定的

所以贴心的 Oracle 为我们准备了拿日期类型对象直接增减月份的函数

ADD_MONTHS(date, integer)

比如说下个月就是 add_months(sysdate, 1)，上个月就是 add_months(sysdate, -1)

但是要注意了，只能整月份的加减，比如 0.5 不能代表半个月，输入参数的小数部分只会被截断

SQL> SELECT add_months(SYSDATE, 1) add_1, add_months(SYSDATE, -0.5) diff_0d5
  2    FROM dual;


ADD_1                DIFF_0D5
-------------------- --------------------
2020/10/14 22:01:38  2020/9/14 22:01:38

months_between 则是用以计算两个日期类型量之间相差的月份数

MONTHS_BETWEEN(date1, date2)

它将计算两个日期之间的月份数，并且总是用 date1 减去 date2

这个函数值得注意的是，人家是把每个月都看成 31 天来算小数部分的

也就是说，每相差一天就是相差 1/31 月，一小时就是 1/31/24 月，以此类推

SQL> SELECT months_between(:l_feb_15th_12, :l_dec_31st_0) mb
  2        ,(:l_feb_15th_12 - :l_dec_31st_0)  31 mb2
  3    FROM dual;


        MB        MB2
---------- ----------
       1.5        1.5
l_feb_15th_12
---------
2020/2/15 12:00:00
l_dec_31st_0
---------
2019/12/31

NEXT_DAY

NEXT_DAY(date, char)

next_day 可以分秒不差的返回由 char 指定的 date 以后的下一个星期几的时间

char 参数可以参考前文模板元素中的 D 或者 DAY 都可以，但是这个函数不能再指定 NLS 参数了，所以使用 DAY 模板的话必须符合数据库当前的 NLS_DATE_LANGUAGE 参数的格式

SQL> SELECT to_char(SYSDATE, 'day') str_day
  2        ,to_char(SYSDATE, 'd') str_d
  3        ,next_day(SYSDATE, '星期一') nd
  4        ,next_day(SYSDATE, 3) nd2
  5    FROM dual;


STR_DAY      STR_D ND                   ND2
------------ ----- -------------------- --------------------
星期一       2     2020/9/21 22:12:50   2020/9/15 22:12:50


SQL> --查看当前的NLS_DATE_LANGUAGE参数
SQL> SELECT *
  2    FROM nls_session_parameters
  3   WHERE parameter = 'NLS_DATE_LANGUAGE';


PARAMETER            VALUE
-------------------- --------------------
NLS_DATE_LANGUAGE    SIMPLIFIED CHINESE


SQL> SELECT next_day(SYSDATE, 'MONDAY') FROM dual;
SELECT next_day(SYSDATE, 'MONDAY') FROM dual


ORA-01846: 周中的日无效

LAST_DAY

计算月底最后一天

SQL> SELECT last_day(SYSDATE) end_of_month FROM dual;


END_OF_MONTH
--------------------
2020/9/30 22:25:06

（未完待续）

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle日期操作函数不完全简介（上篇）

评论