Oracle时间查询语句合集

1.当前日期的年份第一天和最后一天

SELECT TRUNC(SYSDATE,'Y') RQ FROM DUAL;

select last_day(add_months(trunc(SYSDATE,'y'),11)) from dual

2.当前日期的月份第一天和最后一天

select trunc(sysdate,'MM') from dual;

输出结果2022/2/1
3.30天前日期

SELECT TRUNC(SYSDATE-30)  RQ FROM DUAL

4.当天

SELECT TRUNC(SYSDATE,'DD') RQ FROM DUAL

5.当日（智能调节）

SELECT CASE
                 WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) >= 17 THEN
                  TRUNC(SYSDATE, 'DD')
                 ELSE
                  TRUNC(SYSDATE, 'DD') - 1
       END RQ
FROM   DUAL

6.当月

SELECT
  CASE
    WHEN TRUNC(SYSDATE) = TRUNC(LAST_DAY(SYSDATE)) THEN TO_CHAR(SYSDATE, 'YYYYMM')
    ELSE TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYYMM')
  END NY
FROM
  DUAL

7.当月智能调节

SELECT CASE
                 WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < TO_NUMBER(TO_CHAR(last_day(SYSDATE), 'DD'))   THEN
                  TO_CHAR(add_months(last_day(sysdate), -1),'YYYYMM') 
                 ELSE
                   TO_CHAR(SYSDATE,'YYYYMM') 
       END NY
FROM   DUAL

8.去年本月

SELECT
  CASE
    WHEN TRUNC(SYSDATE) = TRUNC(LAST_DAY(SYSDATE)) THEN TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYYMM')
    ELSE TO_CHAR(ADD_MONTHS(SYSDATE,-12), 'YYYYMM')
  END NY
FROM
  DUAL

9.当前年月

select to_char(sysdate,'yyyyMM') AS NY
from dual

10.截取年月

select * from CLZ_SX_PROD_RB t
where substr(TO_CHAR(RQ,'yyyymmdd'),0,6)= '202202'