SELECT CONVERT(varchar(15), DATEADD(DAY,number, '2024-01-01'), 23) days
FROM master..spt_values
WHERE type='p'
AND number<=DATEDIFF(DAY, '2024-01-01', '2024-03-01')
SELECT
DATE_ADD('start_date', INTERVAL n DAY) AS generated_date
FROM (
SELECT
(a + b * 10 + c * 100 + d * 1000) AS n
FROM
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,
(SELECT 0 AS b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b,
(SELECT 0 AS c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c,
(SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d
) AS numbers
WHERE DATE_ADD('start_date', INTERVAL n DAY) <= 'end_date';
方法2:
WITH RECURSIVE date_range AS (
SELECT 'start_date' AS generated_date
UNION ALL
SELECT DATE_ADD(generated_date, INTERVAL 1 DAY)
FROM date_range
WHERE generated_date < 'end_date'
)
SELECT generated_date
FROM date_range;