sql输入年份获取年、月、周

sql输入年份获取年、月、周

SELECT
to_char(trunc(DATES,‘ww’),‘yyyy’) AS YEAR,
to_char(trunc(DATES,‘ww’),‘MM’) AS MONTH,
to_char(DATES,‘ww’) AS WEEK,
COUNT (*) AS TOTALS
FROM(SELECT TRUNC(TO_DATE(‘2000’,‘YYYY’), ‘YYYY’) + ROWNUM - 1 AS DATES
FROM DUAL CONNECT BY ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE(‘2000’,‘YYYY’), ‘YYYY’), 12) - TRUNC(TO_DATE(‘2000’,‘YYYY’), ‘YYYY’)
)
GROUP BY
to_char(trunc(DATES,‘ww’),‘yyyy’),
to_char(trunc(DATES,‘ww’),‘MM’),
to_char(DATES,‘ww’)
ORDER BY
to_char(trunc(DATES,‘ww’),‘MM’) ASC,
to_char(DATES,‘ww’) ASC