oracle日期格式数据操作

以下假定sysdate为 2019-12-30 10:10:10

1. trunc

作用：对日期进行截取

说明：（1）trunc截取完date格式的数据后，依然是date格式

select trunc(sysdate, 'yyyy') from dual; -- 2019-01-01 00:00:00对应日期
select trunc(sysdate, 'mm') from dual; -- 2019-12-01 00:00:00对应日期
select trunc(sysdate, 'dd') from dual; -- 2019-12-30 00:00:00对应日期

（2）如何验证 trunc(sysdate, 'yyyy') 对应的日期是否为2019-01-01 00:00:00呢？

select to_char(trunc(sysdate, 'yyyy'), 'yyyy-mm-dd hh24:mi:ss') from dual;

（3）如果截取的是yyyy，会把sysdate中的月、日、时、分、秒给置换成初始值（月和日最小就是01，时、分、秒最小就是00）

如果截取的是mm，会把sysdate中的日、时、分、秒给置换成初始值

如果取其他的格式字符，可以发现类似规律

2. to_char

作用：日期格式转字符串格式

说明：（1）将日期转换成格式化字符串，to_char操作后就是字符串类型了

比如：将sysdate转化成短日期格式 2019-12-30，

select to_char(sysdate, 'yyyy-mm-dd') from dual;

（2）截取sysdate中的某个部分

select to_char(sysdate, 'yyyy') as yearStr from dual; -- （只查看年）2019
select to_char(sysdate, 'mm') as monthStr from dual; -- （只查看月）12
select to_char(sysdate, 'dd') as dayStr from dual; -- （只查看日）30
select to_char(sysdate, 'q') as quarterStr from dual; -- （只查看季度）4

3. add_months

作用：将指定日期增加或减少几个月

说明：

（1）基本使用

add_months操作后还是日期格式

select add_months(sysdate, 1) from dual; -- 2020-1-30 10:10:10对应日期（将当前时间向后推移1个月）
select add_months(sysdate, -1) from dual; -- 2019-11-30 10:10:10对应日期（将当前时间向前推移1个月）

（2）如果要增加1年呢？

select add_months(sysdate, 1*12) from dual; -- 2020-12-30 10:10:10对应日期（将当前时间向后推移1年）

n年（n>0就是向后推移，n<0就是向前推移）的话就是

select add_months(sysdate, n*12) from dual;

 

如果要增加1天呢？

select sysdate+1 from dual;

n天（n>0就是向后推移，n<0就是向前推移）的话就是

select sysdate+n from dual;

 

综合应用

1. 检查当前时间是否为本月的20号

to_char(sysdate, 'yyyy-mm-dd') = to_char(trunc(sysdate, 'mm') + 19, 'yyyy-mm-dd')

验证（其他验证日期比较条件是否正确的也可以借用下面这种方法）：

select 'AA' from dual where to_char(sysdate, 'yyyy-mm-dd') = to_char(trunc(sysdate, 'mm') + 19, 'yyyy-mm-dd'); -- 显然2019-12-30不是所在月份（12月）的20日，查询没有数据

select 'AA' from dual where to_date('2019-12-20', 'yyyy-mm-dd') = trunc(sysdate, 'mm') + 19; -- 显然2019-12-20是所在月份（12月）的20日，查询有数据

扩展：存储过程中if判断条件中写成

begin
   -- 检查当前时间是否为本月的20号（这里要注意的是判断相等，要写=，而不是==）
   if to_char(sysdate, 'yyyy-mm-dd') = (to_char(trunc(sysdate, 'mm') + 19, 'yyyy-mm-dd')) then
     dbms_output.put_line('是本月20日');
   end if;
   -- 检查当前时间是否不是本月的20号
   if to_char(sysdate, 'yyyy-mm-dd') != (to_char(trunc(sysdate, 'mm') + 19, 'yyyy-mm-dd')) then
     dbms_output.put_line('不是本月20日'); -- 会打印
   end if;
   -- 检查当前时间是否处在本月下旬（从21日到月底）
   if to_char(sysdate, 'yyyy-mm-dd') > (to_char(trunc(sysdate, 'mm') + 19, 'yyyy-mm-dd')) then
     dbms_output.put_line('是本月下旬'); -- 会打印
   end if;
end;

 

2. 检查当前时间是否为当季度的最后一个月的20号

to_char(sysdate, 'yyyy-mm-dd') = to_char(add_months(trunc(sysdate, 'q'), 2)+19, 'yyyy-mm-dd')

说明：

select to_char(add_months(trunc(sysdate, 'q'), 2)+19, 'yyyy-mm-dd') from dual; -- 2019-12-20