ORACLE 获取某一年中的所有周末日期

ORACLE 获取某一年中的所有周末日期

思路是先获取一整年的所有日期，标准星期，再做筛选

SELECT
	CHINA_WEEK AS WEEK ,TO_DATE(DATETIME, 'yyyy-MM-dd') 
FROM
	(
	SELECT
		to_char(TO_DATE('2018-12-31', 'yyyy-MM-dd') - LEVEL, 'd') AS week,
		decode(to_char(TO_DATE('2018-12-31', 'yyyy-MM-dd') - LEVEL, 'd'), 1, '星期一', 2, '星期二', 3, '星期三', 4, '星期四', 5, '星期五', 6, '星期六', 7, '星期日') AS china_week,
		TO_CHAR((TO_DATE('2018-12-31', 'yyyy-MM-dd') - (ROWNUM - 1)), 'yyyy-MM-dd') AS datetime
	FROM
		dual a
	CONNECT BY
		LEVEL <= trunc(TO_DATE('2018-12-31', 'yyyy-MM-dd') - TO_DATE('2018-01-01', 'yyyy-MM-dd')+ 1)
	ORDER BY
		datetime) d
WHERE
	d.week IN (6, 7)