oracle sql查询日期

查询今日每一分钟

SELECT CONCAT(TO_CHAR(Trunc(SYSDATE)+(ROWNUM-1)/24, 'YYYY-MM-DD HH24'),':00:00') timeM 
     FROM DUAL CONNECT BY ROWNUM <= 24

查询结果:

2020-11-10 00:00:00  2020-11-10 01:00:00  2020-11-10 02:00:00  2020-11-10 03:00:00
2020-11-10 04:00:00  2020-11-10 05:00:00  2020-11-10 06:00:00  2020-11-10 07:00:00
2020-11-10 08:00:00  2020-11-10 09:00:00  2020-11-10 10:00:00  2020-11-10 11:00:00
2020-11-10 12:00:00  2020-11-10 13:00:00  2020-11-10 14:00:00  2020-11-10 15:00:00
2020-11-10 16:00:00  2020-11-10 17:00:00  2020-11-10 18:00:00  2020-11-10 19:00:00
2020-11-10 20:00:00  2020-11-10 21:00:00  2020-11-10 22:00:00  2020-11-10 23:00:00

查询本周每一天

 SELECT to_char(TRUNC(NEXT_DAY(SYSDATE-8,1)+ROWNUM),'YYYY-MM-DD') timeM
    FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TRUNC(sysdate)- TO_DATE(TRUNC(NEXT_DAY(SYSDATE-8,1)+0)))

-- 查询结果
2020-11-09  2020-11-10  2020-11-11  

查询本月每一天

SELECT TO_CHAR(TRUNC(SYSDATE, 'MM') + ROWNUM - 1, 'YYYY-MM-DD') timeM
     FROM DUAL
     CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'))
-- 查询结果
2020-11-01  2020-11-02  2020-11-03  2020-11-04  2020-11-05
2020-11-06  2020-11-07  2020-11-08  2020-11-09  2020-11-10
2020-11-11  2020-11-12  2020-11-13  2020-11-14  2020-11-15
2020-11-16  2020-11-17  2020-11-18  2020-11-19  2020-11-20
2020-11-21  2020-11-22  2020-11-23  2020-11-24  2020-11-25
2020-11-26  2020-11-27  2020-11-28  2020-11-29  2020-11-30

查询一段时间的最后一天

select to_char(last_day(add_months(to_date('20190701','yyyymmdd'),rownum-1)), 'yyyymmdd') busi_dt
from dual
connect by rownum<=months_between(to_date('20200501','yyyymmdd'),to_date('20190701','yyyymmdd'))

-- 查询结果
  20190731
  20190831
  20190930
  20191031
  20191130
  20191231
  20200131
  20200229
  20200331
  20200430

 查询最近一年

SELECT TO_CHAR( ADD_MONTHS( SYSDATE, - ROWNUM + 1 ), 'yyyy-mm' ) month FROM DUAL CONNECT BY ROWNUM <= 13
-- 查询结果:
2020-11  2020-10  2020-09  2020-08  2020-07
2020-06  2020-05  2020-04  2020-03  2020-02
2020-01  2019-12  2019-11

查询一段时间的年末 

SELECT * FROM ((select to_char(last_day(add_months(to_date('20131231','yyyymmdd'),rownum-1)), 'yyyymmdd') busi_dt
   from dual connect by rownum<=months_between(to_date('20221231','yyyymmdd'),to_date('20131231','yyyymmdd')))) 
WHERE busi_dt LIKE '%%1231'

-- 查询结果
20131231
20141231
20151231
20161231
20171231
20181231
20191231
20201231
20211231

格式化日期 

select to_char(TO_DATE(biz_date, 'yyyymmdd'),'yyyy/mm/dd')   bizDate from dual
-- 查询格式结果: 
  2019/12/31

查询3年前的日期

select to_char(add_months(to_date('20220531','yyyymmdd'),-36),'yyyymmdd') from dual
-- 查询结果
20190531

查询3年前的年末

select concat(substr(busi_dt, 1, 4),'1231') 
from (select to_char(add_months(to_date('20220531','yyyymmdd'),-36),'yyyymmdd') busi_dt from dual) -- 查询结果 20191231

 

posted on 2020-11-10 15:35  蜗牛也是妞  阅读(2162)  评论(0编辑  收藏  举报