年度周次本周开始时间本周结束时间上周所对应年上周所对应周oracle数据库,我想获取这6个字段,该怎么写,每周从周一到周日视为一周,写了很多sql到跨年的时候都处理不好,有什么帆软公式可以替代吗?或者下面的sql怎么修改能保证跨年时数据展示正确WITH DateInfo AS ( SELECT TO_CHAR(dt, 'YYYY') AS Year, TO_CHAR(dt, 'IW') AS Week, TRUNC(dt, 'IW') AS WeekStart, TRUNC(dt, 'IW') + 6 AS WeekEnd, TO_CHAR(TRUNC(dt, 'IW') - 7, 'YYYY') AS LastYear, TO_CHAR(TRUNC(dt, 'IW') - 7, 'IW') AS LastWeek FROM ( SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 AS dt FROM dual CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') ))SELECT DISTINCT Year, Week, TO_CHAR(WeekStart, 'YYYY/MM/DD HH24:MI') AS WeekStart, TO_CHAR(WeekEnd, 'YYYY/MM/DD HH24:MI') AS WeekEnd, LastYear, LastWeekFROM DateInfoWHERE Year = TO_CHAR(SYSDATE, 'YYYY')ORDER BY WeekStart;