[Oracle] 获取系统当前日期

获取Oracle系统当前日期，我们可以考虑使用SYSDATE的参数

SELECT 
    SYSDATE AS sdate,                                 -- 系统完整日期时间
    TO_CHAR(SYSDATE, 'YYYY')  AS "sdate_year4",       -- 年(4位)
    TO_CHAR(SYSDATE, 'YY')    AS "sdate_year2",       -- 年(2位)
    TO_CHAR(SYSDATE, 'MM')    AS "sdate_month2",      -- 月(数字)
    TO_CHAR(SYSDATE, 'MON')   AS "sdate_mon",         -- 月(数字+月)
    TO_CHAR(SYSDATE, 'DD')    AS "sdate_day",         -- 日
    TO_CHAR(SYSDATE, 'IW')    AS "sdate_week",        -- 周数
    TO_CHAR(SYSDATE, 'Q')     AS "sdate_quarter",     -- 季度(1-4)
    -- 季节
    CASE 
        WHEN TO_CHAR(SYSDATE, 'MM') IN ('12','01','02') THEN '冬季'
        WHEN TO_CHAR(SYSDATE, 'MM') IN ('03','04','05') THEN '春季'
        WHEN TO_CHAR(SYSDATE, 'MM') IN ('06','07','08') THEN '夏季'
        ELSE '秋季'
    END AS "sdate_season",
    TO_CHAR(SYSDATE, 'AM')    AS "sdate_ampm",        -- AM/PM
    TO_CHAR(SYSDATE, 'HH24')  AS "sdate_hour24",      -- 小时(24小时制)
    TO_CHAR(SYSDATE, 'MI')    AS "sdate_minute",      -- 分钟
    TO_CHAR(SYSDATE, 'SS')    AS "sdate_second",      -- 秒钟
    TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1, 'DD') AS "sdate_month_days",  -- 当月天数
    TO_CHAR(LAST_DAY(SYSDATE), 'MM-DD') AS "sdate_last_day_of_month",              -- 当月最后一天
    -- YYYY-MM-DD HH:MI:SS
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')AS "sdate_iso_format",                
    -- YYYY年MM月DD日
    TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') AS "sdate_chinese_format"             
FROM DUAL;

日期时间也可进行加减乘除的运算

-- 当前时间减去6分钟的时间
SELECT sysdate, sysdate - interval '6' minute AS res1 FROM dual;
-- 当前时间减去6小时的时间
SELECT sysdate, sysdate - interval '6' hour AS res2 FROM dual;
-- 当前时间减去6天的时间
SELECT sysdate, sysdate - interval '6' day AS res3 FROM dual;
-- 当前时间减去6月的时间
SELECT sysdate, sysdate - interval '6' month AS res4 FROM dual;
-- 当前时间减去6年的时间
SELECT sysdate, sysdate - interval '6' year AS res5 FROM dual;
-- 时间间隔乘以一个数字
SELECT sysdate, sysdate - 8*interval '6' hour AS res6 FROM dual;

输出结果：

补充扩展：Oracle获取当月所有日期

SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));