How to Generate a Date Range in MySQL Using a SELECT Query?

Use MySQL SELECT query to generate date range

Many applications need to get a series of dates within a specified date range. The MySQL SELECT query accomplishes this.

Query construction

To generate a list of dates between two given dates, you can use the following query:

<code class="language-sql">SELECT * FROM
(SELECT ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) AS selected_date FROM
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE selected_date BETWEEN '2012-02-10' AND '2012-02-15';</code>

This query generates a date range from '2012-02-10' to '2012-02-15', with each date displayed as a single row in the result set.

Description

A nested SELECT statement in the main query is used to construct a list of dates. Each SELECT statement generates a sequence of one-digit numbers from 0 to 9. These sequences are then combined using the " " operator to form a complete date string. The "ADDDATE" function is used to add these numbers to '1970-01-01' to generate the desired date range.

The above is the detailed content of How to Generate a Date Range in MySQL Using a SELECT Query?. For more information, please follow other related articles on the PHP Chinese website!