Oracle12C--日期格式（二十三）

知识点的梳理：

• 日期操作公式：
  • 日期 - 数字 = 日期；
  • 日期 + 数字 = 日期；
  • 日期 - 日期 = 数字（天数）；
  • 可以使用 1/24表示1小时,1/24/60表示1分钟,依次类推
  • 注意：在Java 中，j ava.util.Date类的对象可以变为长整型数据，而在Oracle中日期也可以和数字进行转换；
    • 所以日期 - 日期的结果就是数字。
    • 不存在"日期 + 日期"的操作，试想"1978年07月27日 + 1981年09月29日"的结果是什么？没人会知道答案
• 通过sysdate 伪列取得的只是"年月日时分秒"等数据，想精确到毫秒，则应该使用systimestamp 伪列：
  • select systimestamp from dual;
• 输出当前系统时间：select sysdate from dual;

    

    

• 常用日期相关sql
  • oracle使用关键字sysdate表示系统当前的日期时间 , 可以直接使用
    • 注意：在插入数据时，我们可以使用字符串表示数据，只要符合当前的日期格式

select sysdate from dual; select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;	oracle输出当前的日期默认格式信息
select * from v$nls_parameters;	查看日期的输出格式
alter session set nls_date_format = 'yyyy-mm-dd'	修改本次登录的日期格式

• 对sysdate直接进行加减运算,加减单位是天,:

select sysdate+1 from dual;	明天的日期
to_date('2015-11-11' , 'yyyy-mm-dd') ;	把特定格式的字符串转换为日期数据

• 详解Oracle的日期
  • Oracle 用 Date 这个内置的数据类型来存储日期和时间。它可以存储年月日和时分秒；
  • 没有只存储时间或只存储日期的单独数据类型；
  • 当显示Date类型的数据时，Oracle 先要把存储的值转化为字符串。通常，这种转换是通过 TO_CHAR 这个函数来完成的。如果没有用 TO_CHAR 函数来指定特定的日期时间格式，Oracle 默认的显示格式是"DD-MON-YY"；
    • 例如：
      • 创建一个表 x：

        create table abc(a int, b date);

      • 插入一行数据：

        insert into abc(b) values (to_date('2017-01-01','yyyy-mm-dd'));

      • 然后执行下面的查询语句：

        select b from abc;

      • 结果如下(set head on):

语言是英语的显示结果	语言是中文的显示结果
B ------------  01-APR-09	B -------------  01-四月 -09

• 下面是显式地调用 TO_CHAR 函数，并指定自己想要的格式。例如：

  SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b FROM abc;

  • 返回的结果：

B ------------  2010/09/01

• 在显示时不管用什么格式都不会影响我们实际储存的数据；所以在插入时，单纯采用 Oracle 的默认格式就不是那么符合需求了；
  • 我们还是需要显式调用 TO_DATE 函数，例如：

insert into abc values(99, to_date('2008/05/31:12:00:00', 'yyyy/mm/dd:hh:mi:ss'));

• Oracle 默认的日期格式是"DD-MON-YY"，可以通过如下命令对这个默认格式进行修改。在 SQL*plus 里面输入：

  alter session set NLS_DATE_FORMAT='<my_format>';   --这个改变只对当前的会话(session)有用。"my_format"是期望修改为的日期格式；

  • 例如：

    SQL> altersession set nls_date_format='yyyy-mm-dd hh24:mi:ss';

    会话已更改。

    SQL>select sysdate from dual; --2017-01-01 00:00:00

• 日期参数

模板	描述
HH	一天的小时数 (01-12)
HH12	一天的小时数 (01-12)
HH24	一天的小时数 (00-23)
MI	分钟 (00-59)
SS	秒 (00-59)
SSSS	午夜后的秒 (0-86399)
FM	去掉查询后的前导0，该标记用于时间模板的后缀 注意：从开发角度来说，还是建议保留前导0，这样所有日期格式化后的长度是一样的，方便处理；另外，严格来说，FM不属于日期格式化标记，为了方便记忆，放在了这里
AM or A.M. or PM or P.M.	正午标识（大写）
am or a.m. or pm or p.m.	正午标识（小写）
Y,YYY	带逗号的年（4 和更多位）
YYYY	年（4和更多位）
YYY	年的后三位
YY	年的后两位
Y	年的最后一位
BC or B.C. or AD or A.D.	年标识（大写）
bc or b.c. or ad or a.d.	年标识（小写）
MONTH	全长大写月份名（9字符）
Month	全长混合大小写月份名（9字符）
month	全长小写月份名（9字符）
MON	大写缩写月份名（3字符）
Mon	缩写混合大小写月份名（3字符）
mon	小写缩写月份名（3字符）
MM	月份 (01-12)
DAY	全长大写日期名（9字符）
Day	全长混合大小写日期名（9字符）
day	全长小写日期名（9字符）
DY	缩写大写日期名（3字符）
Dy	缩写混合大小写日期名（3字符）
dy	缩写小写日期名（3字符）
DDD	一年里的日子(001-366)
DD	一个月里的日子(01-31)
D	一周里的日子(1-7；SUN=1)
W	一个月里的周数
WW	一年里的周数
CC	世纪（2 位）
J Julian	日期（自公元前4712年1月1日来的日期）
Q	季度
RM	罗马数字的月份（I-XII；I=JAN）－大写
rm	罗马数字的月份（I-XII；I=JAN）－小写

• 数字格式
  • 示例1：在使用to_char()格式化数字的时候，如果设置的是9，则不够的位数将不显示，如果设置为0，则表示会在前面补0进行显示

select to_char(987654321.789,'999,999,999,999.99999') 格式化数字, to_char(987654321.789,'000,000,000,000.00000') 格式化数字 from dual;

• 示例2：格式化货币显示

select to_char(987654321.789,'L999,999,999,999.99999') 显示货币, to_char(987654321.789,'$999,999,999,999.99999') 显示美元 from dual;
模板	描述
9	代表一个数字
0	前导零的值
.	（句点） 小数点
,	（逗号） 分组（千）分隔符
D	小数点（使用本地化）
G	分组分隔符（使用本地化）
PR	尖括号内负值
S	带负号的负值（使用本地化）
L	货币符号（使用本地化）
MI	在指明的位置的负号（如果数字 < 0）
PL	在指明的位置的正号（如果数字 > 0）
SG	在指明的位置的正/负号
RN	罗马数字（输入在 1 和 3999 之间）
TH or th	转换成序数
V	移动 n 位（小数）（参阅注解）