Oracle SQL时间处理，年，周，月，星期，时间差，月份差，字符串时间互转，时间段计算，年月周星期最后一天，生成年份列表

Oracle SQL 计算时间格式 yyyyMMdd格式
SELECT to_char(sysdate,‘yyyy-MM-dd’) FROM DUAL;

年月日
SELECT to_char(sysdate,‘yyyy"年"MM"月"dd"日"’) FROM DUAL;

年月日时分秒
SELECT to_char(sysdate,‘yyyy"年"MM"月"dd"日"hh"时"mi"分"ss"秒"’) FROM DUAL;

年的周次
SELECT to_char(sysdate,‘yyyy-iw’) FROM DUAL;

SELECT to_char(sysdate,‘yyyy-ww’) FROM DUAL;

查询某天是星期几
中文
select to_char(to_date(‘2012-10-20’,‘yyyy-mm-dd’),‘day’) strDay from dual;
英文
select to_char(to_date(‘2012-10-20’,‘yyyy-mm-dd’),‘day’,‘NLS_DATE_LANGUAGE = English’) strDay from dual;

select to_char(sysdate,‘yyyymmdd’) strDateTime from dual; --获取年月日

select to_char(sysdate,‘yyyy’) strYear from dual; --获取年

select to_char(sysdate,‘mm’) strMonth from dual; --获取月

select to_char(sysdate,‘dd’) strDay from dual; --获取日

select to_char(sysdate,‘hh24’) strHour from dual; --获取时

select to_char(sysdate,‘mi’) strMinute from dual; --获取分

select to_char(sysdate,‘ss’) strSecond from dual; --获取秒

字符串和时间互转
select to_date(‘2024-01-11 11:23:44’,‘yyyy-mm-dd hh12:mi:ss’) dateTime from dual;

select to_char(sysdate,‘yyyy/mm/dd hh24:mi:ss’) dateTime from dual;

select to_char( to_date(222,‘J’),‘Jsp’) from dual;

两个日期间的天数
select floor(sysdate - to_date(‘20161010’,‘yyyymmdd’)) strTime from dual;

时间为null的用法
select to_date(null) from dual;

月份差
select months_between(date’2014-04-23’,date’2013-04-23’) days from dual;

本周
SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,‘yyyy-MM-dd’),‘yyyy-MM-dd’),‘IW’),‘yyyyMMdd’) FROM DUAL;

SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,‘yyyy-MM-dd’),‘YYYY-MM-DD’),‘IW’) + 6,‘yyyyMMdd’) FROM DUAL;

本周
select trunc(sysdate, ‘d’) + 1 from dual;

select trunc(sysdate, ‘d’) + 7 from dual;

本季
select to_char(trunc(sysdate, ‘Q’),‘yyyyMMdd’) from dual;

select to_char(add_months(trunc(sysdate, ‘Q’), 3) - 1,‘yyyyMMdd’) from dual;
yyyy-MM-dd格式

本月
select trunc(sysdate, ‘mm’) from dual;

select last_day(trunc(sysdate)) from dual;

本季
select trunc(sysdate, ‘Q’) from dual;

select add_months(trunc(sysdate, ‘Q’), 3) - 1 from dual;

获取本周整个时间段
SELECT TRUNC(SYSDATE,‘iw’) - 1 + ROWNUM FROM DUAL CONNECT BY ROWNUM <= 7

获取上周整个时间段
SELECT TRUNC(SYSDATE,‘iw’) - 8 + ROWNUM FROM DUAL CONNECT BY ROWNUM <= 7

获取本月整个时间段
SELECT TRUNC(SYSDATE, ‘mm’) - 1 + ROWNUM FROM DUAL CONNECT BY ROWNUM <= TO_CHAR(LAST_DAY(SYSDATE),‘dd’)

获取上月整个时间段
SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), ‘month’) - 1 + ROWNUM FROM DUAL CONNECT BY ROWNUM <= TO_CHAR(LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE, -1), ‘month’)),‘dd’)

获取最近7的整个时间段
SELECT TRUNC(SYSDATE, ‘dd’) - ROWNUM AS day FROM DUAL CONNECT BY ROWNUM <= 7

本周一时间
SELECT TRUNC(SYSDATE, ‘iw’) FROM DUAL

本周末时间
SELECT TRUNC(SYSDATE, ‘iw’) + 6 FROM DUAL

上周一时间
SELECT TRUNC(SYSDATE, ‘iw’) - 7 FROM DUAL

上周末时间
SELECT TRUNC(SYSDATE, ‘iw’) - 1 FROM DUAL

本月第一天
SELECT TRUNC(SYSDATE, ‘mm’) FROM DUAL

本月最后一天
SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), ‘month’) FROM DUAL

30、60、120天前时间
SELECT SYSDATE - 30 FROM DUAL
SELECT SYSDATE - 60 FROM DUAL
SELECT SYSDATE - 120 FROM DUAL

–2024年每周周五
SELECT zw.Friday FROM
(SELECT
to_char(aa.dt, ‘yyyymmdd’) Friday FROM
(SELECT
( to_date( ‘20240101’, ‘yyyymmdd’ ) + ROWNUM ) dt
FROM
dual CONNECT BY ROWNUM <= to_date( ‘20241231’, ‘yyyymmdd’ ) - to_date( ‘20240101’, ‘yyyymmdd’ )
) aa
WHERE
to_char( aa.dt, ‘D’ ) = ‘6’
ORDER BY
dt ) zw

–2024年每月最后一天
SELECT
to_char( add_months( last_day( to_date( ‘20240101’, ‘yyyymmdd’ ) ),+ ROWNUM - 1 ), ‘yyyymmdd’ ) AS lastdate
FROM
dual CONNECT BY ROWNUM <= months_between( to_date( ‘20241231’, ‘yyyymmdd’ ), to_date( ‘20240101’, ‘yyyymmdd’ ))

–2020年到2099年 年份列表
SELECT 2020+level-1 nf FROM DUAL CONNECT BY LEVEL <=2099-2020+1