oracle获取本周,本月,本年第一天和最后一天
获取本周第一天和最后一天:
-- 获取某个日期周一 SELECT CASE to_number(to_char(to_date('2022-05-08', 'yyyy-MM-dd'), 'D')) WHEN 7 THEN to_date('2022-05-08', 'yyyy-MM-dd') - 5 WHEN 6 THEN to_date('2022-05-08', 'yyyy-MM-dd') - 4 WHEN 5 THEN to_date('2022-05-08', 'yyyy-MM-dd') - 3 WHEN 4 THEN to_date('2022-05-08', 'yyyy-MM-dd') - 2 WHEN 3 THEN to_date('2022-05-08', 'yyyy-MM-dd') - 1 WHEN 2 THEN to_date('2022-05-08', 'yyyy-MM-dd') ELSE to_date('2022-05-08', 'yyyy-MM-dd') - 6 END AS curmon FROM dual; -- 获取某个日期周日 SELECT CASE to_number(to_char(to_date('2022-05-13', 'yyyy-MM-dd'), 'D')) WHEN 7 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 1 WHEN 6 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 2 WHEN 5 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 3 WHEN 4 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 4 WHEN 3 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 5 WHEN 2 THEN to_date('2022-05-13', 'yyyy-MM-dd') + 6 ELSE to_date('2022-05-13', 'yyyy-MM-dd') END AS curmon FROM dual; -- 获取本周周一 SELECT CASE to_number(to_char(sysdate, 'D')) WHEN 7 THEN sysdate - 5 WHEN 6 THEN sysdate - 4 WHEN 5 THEN sysdate - 3 WHEN 4 THEN sysdate - 2 WHEN 3 THEN sysdate - 1 WHEN 2 THEN sysdate ELSE sysdate - 6 END AS curmon FROM dual; -- 获取本周周日 SELECT CASE to_number(to_char(sysdate, 'D')) WHEN 7 THEN sysdate + 1 WHEN 6 THEN sysdate + 2 WHEN 5 THEN sysdate + 3 WHEN 4 THEN sysdate + 4 WHEN 3 THEN sysdate + 5 WHEN 2 THEN sysdate + 6 ELSE sysdate END AS curmon FROM dual;
获取本月第一天和最后一天:
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyymmdd') "本月第一天", to_char(last_day(sysdate), 'yyyymmdd') "本月最后一天" from dual;
获取本年第一天和最后一天:
-- 本年第一天 select trunc(sysdate,'yyyy') FROM DUAL; -- 本年最后一天 select last_day(add_months(trunc(SYSDATE,'y'),11)) from dual
学如逆水行舟,不进则退