oracle 实现sql语句日期的 年 、月 、日 加减

介绍:

       获取当前时间, 往后加一个年 、  加一月 、  加一天 、举个例子，我想把2026年加一年变成 2027年。 查询或insert添加都可以对日期加减。

1、年

select TO_CHAR(t.RECEPTION_TIME + numtoyminterval(1, 'year'),'YYYY-MM-DD HH24:MI') AS dateTime from CUSTOM_CABINETS_CHECKING t

也可以这样 

select ADD_MONTHS(t.RECEPTION_TIME,12) AS dateTime from CUSTOM_CABINETS_CHECKING t

2、 月

select TO_CHAR(t.RECEPTION_TIME + numtoyminterval(1, 'month'),'YYYY-MM-DD HH24:MI') AS dateTime from CUSTOM_CABINETS_CHECKING t

 3、日

select TO_CHAR(t.RECEPTION_TIME + numtodsinterval(1,'day'),'YYYY-MM-DD HH24:MI') AS dateTime from CUSTOM_CABINETS_CHECKING t

       注意:  加一天的函数(numtodsinterval)与年、 月、函数(numtoyminterval)不一样

1、正常日期查询

select PARENT_ID       AS uuid,
       SHIPMENTST_TIME AS dateTime1,
       DELIVERY_TIME   AS dateTime2,
       RECEPTION_TIME  AS dateTime
  from CUSTOM_CABINETS_CHECKING t

查询结果如下:

 2、年份相加~对DATETME 进行 

select t.PARENT_ID       AS uuid,
       t.SHIPMENTST_TIME AS dateTime1,
       t.DELIVERY_TIME   AS dateTime2,
       TO_CHAR(t.RECEPTION_TIME+numtoyminterval(1,'year') ,'YYYY-MM-DD HH24:MI') AS dateTime
  from CUSTOM_CABINETS_CHECKING t

查询结果:

 3、年份相加~对DATETME 进行 (另一种写法) 

select t.PARENT_ID       AS uuid,
       t.SHIPMENTST_TIME AS dateTime1,
       t.DELIVERY_TIME   AS dateTime2, 
       ADD_MONTHS(t.RECEPTION_TIME,12) AS dateTime
  from CUSTOM_CABINETS_CHECKING t

查询结果: 

 

 4、年份相减~对DATETME 进行  

select t.PARENT_ID       AS uuid,
       t.SHIPMENTST_TIME AS dateTime1,
       t.DELIVERY_TIME   AS dateTime2,
       TO_CHAR(t.RECEPTION_TIME+numtoyminterval(-1,'year') ,'YYYY-MM-DD HH24:MI') AS dateTime
  from CUSTOM_CABINETS_CHECKING t

查询结果:

 总结:  要注意的是加一天的函数(numtodsinterval) 是和年、 月、函数(numtoyminterval)不同。