用SQL语句获取常见的几种日期

用SQL语句获取常见的几种日期数值

---上周
SELECT TO_CHAR(TRUNC(SYSDATE, "IW") - 7, "yyyymmdd") FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE, "IW") - 1, "yyyymmdd") FROM DUAL

---本周
SELECT TO_CHAR(TRUNC(SYSDATE, "IW") , "yyyymmdd") FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE, "IW") + 6, "yyyymmdd") FROM DUAL

---上一月的第一天和最后一天
select trunc(add_months(sysdate, -1), "mm") first_day,
       last_day(add_months(sysdate, -1)) last_day
  from dual
---本月的最后一天
SELECT TO_CHAR(last_day(add_months(sysdate, 0)), "yyyymmdd") FROM DUAL

---当年 第一天
SELECT TO_CHAR(trunc(sysdate, "yyyy"), "yyyymmdd") FROM DUAL
---上年最后一天
SELECT TO_CHAR(trunc(sysdate,"iyyy"), "yyyymmdd") FROM DUAL

---当月第一天
SELECT TO_CHAR(trunc(sysdate,"mm"), "yyyymmdd") FROM DUAL
---当月数值
SELECT TO_CHAR(sysdate,"mm") FROM DUAL

---当周数值
SELECT TO_CHAR(sysdate,"ww") FROM DUAL