ORACLE常用时间查询

陈舟的舟

已于 2022-08-23 09:56:37 修改

阅读量1.5k

点赞数 1

分类专栏： # oracle 文章标签： oracle

于 2020-10-11 00:51:04 首次发布

本文链接：https://blog.csdn.net/qq_37067752/article/details/109006976

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

ORACLE 常用时间查询

–取当前日期的本月末

SELECT TO_CHAR(LAST_DAY(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'MONTH')),'YYYYMMDD') 本月末 FROM DUAL;

SELECT TO_CHAR(LAST_DAY(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'MM')),'YYYYMMDD') 本月末 FROM DUAL;

–取当前日期的上个月末

SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),-1)),'YYYYMMDD') 上个月末 FROM DUAL;

SELECT TO_CHAR(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'MM')-1,'YYYYMMDD') 上个月末 FROM DUAL;

–取当前日期的下个月末

SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),1)),'YYYYMMDD') 下个月末 FROM DUAL;

–取当前日期的本月初

SELECT TO_CHAR(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'MM'),'YYYYMMDD') 本月初 FROM DUAL;

–取当前日期的上个月初

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),-1),'MM'),'YYYYMMDD') 上个月初 FROM DUAL;

–取当前日志的下个月初

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),1),'MM'),'YYYYMMDD') 下个月初 FROM DUAL;

–取上个月的今天

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),-1),'YYYYMMDD') 上个月的今天 FROM DUAL;

–取去年今天

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(20200903,'YYYYMMDD'),-12),'YYYYMMDD') 去年今天 FROM DUAL;

–取今年年末日期

SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'YYYY'),12)-1,'YYYYMMDD') 今年末 FROM DUAL;

–取去年年末日期

SELECT TO_CHAR(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'YY')-1,'YYYYMMDD') 去年年末 FROM DUAL;

–取去年年初日期

SELECT TO_CHAR(TRUNC(TO_DATE(20200903,'YYYYMMDD'),'YY')- INTERVAL '1' YEAR,'YYYYMMDD') 去年年初 FROM DUAL;

–取当前日期

SELECT TO_CHAR(TO_DATE(20200903,'YYYYMMDD'),'YYYYMMDD') 当前日期 FROM DUAL;

–取昨天日期

SELECT  TO_CHAR(TO_DATE(20200903,'YYYYMMDD')-1,'YYYYMMDD') 昨天 FROM DUAL;

–取明天日期

SELECT  TO_CHAR(TO_DATE(20200903,'YYYYMMDD')+1,'YYYYMMDD') 明天 FROM DUAL;

–取当前时间精确到5位毫秒

SELECT TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHHMMSSFF5') 当前毫秒5 FROM DUAL;

–取当月中的每一天

SELECT TO_CHAR(TO_DATE(20200401,'YYYYMMDD') + LEVEL-1,'YYYYMMDD') DT FROM DUAL CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE(20200401,'YYYYMMDD')),'DD');

SELECT TO_CHAR(TO_DATE(TO_CHAR(TRUNC(TO_DATE(20190203,'YYYYMMDD'),'MM'),'YYYYMMDD'),'YYYYMMDD') + LEVEL-1,'YYYYMMDD') DT FROM DUAL CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE(20190203,'YYYYMMDD')),'DD');

–取当月到今天为止的每一天

SELECT TO_CHAR(TO_DATE(TO_CHAR(TRUNC(TO_DATE(20190203,'YYYYMMDD'),'MM'),'YYYYMMDD'),'YYYYMMDD') + LEVEL-1,'YYYYMMDD') DT FROM DUAL CONNECT BY LEVEL <= TO_CHAR(TO_DATE(20190203,'YYYYMMDD'),'DD');

–指定周数，求当年该周的时间范围

SELECT * FROM ( SELECT LEVEL AS week,  
DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,  
  NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) AS 当周第一天,  
DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,  
  NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) + 6 AS 当周最后一天  
  FROM DUAL D  
  LEFT JOIN (SELECT TRUNC(TO_DATE('2021-01-01','YYYY-MM-DD'),'YYYY') AS SD FROM DUAL) PM ON 1=1  
CONNECT BY LEVEL<=53)dd 
 WHERE dd.week = 33;

–查询一段时间范围20220701-20220801

select to_char(datekeey,'yyyymmdd') riqi from (  
    select
        date'2022-07-01' + (rownum - 1)  datekeey
    from  dual connect by rownum <= (date'2022-08-01' - date'2022-07-01' + 1)
    order by datekeey
) order by datekeey;