SQL查询查询某日到某日的全部日期
大家好!我是盒子,这是我第一篇博客,希望大家多多指导
查询某日到某日的全部日期
WITH DATE_TEMP_TABLE (DATELIST)
AS (VALUES (DATE (TO_DATE ('2022-09-01', 'yyyy-MM-dd')))
UNION ALL
SELECT DATELIST + 1 DAY
FROM DATE_TEMP_TABLE
WHERE DATELIST + 1 DAY <=
DATE (TO_DATE ('2022-09-06', 'yyyy-MM-dd')))
SELECT TO_CHAR (DATELIST, 'yyyy-MM-dd') AS DATE_CODE FROM DATE_TEMP_TABLE
查询某月到某月的全部日期
WITH DATE_TEMP_TABLE (DATELIST)
AS (VALUES (DATE (TO_DATE ('2022-01', 'yyyy-MM')))
UNION ALL
SELECT DATELIST + 1 MONTH
FROM DATE_TEMP_TABLE
WHERE DATELIST + 1 MONTH <= DATE (TO_DATE ('2022-08', 'yyyy-MM')))
SELECT
TO_CHAR (DATELIST, 'yyyy-MM') AS DATE_CODE
FROM DATE_TEMP_TABLE
查询某年到某年的全部日期
WITH DATE_TEMP_TABLE (DATELIST)
AS (VALUES (DATE (TO_DATE ('2016', 'yyyy')))
UNION ALL
SELECT DATELIST + 1 YEAR
FROM DATE_TEMP_TABLE
WHERE DATELIST + 1 YEAR <= DATE (TO_DATE ('2021', 'yyyy')))
SELECT
TO_CHAR (DATELIST, 'yyyy') AS DATE_CODE
FROM DATE_TEMP_TABLE
使用的话就左右链接就可以了,很方便。