How to Generate a List of Dates Between Two Dates in MySQL and SQL Server?

Generate a list of dates between two dates

MySQL provides standard functions to retrieve a list of dates within a specified date range. The steps are as follows:

1. Create stored procedure:

Create a stored procedure to generate the required date range. make_intervals Stored procedures can create different types of intervals, including days, minutes, and seconds.

2. Process code:

<code class="language-sql">CREATE PROCEDURE make_intervals(
    startdate timestamp, 
    enddate timestamp, 
    intval integer, 
    unitval varchar(10)
)
BEGIN
    -- ... (存储过程代码，请参考答案中的完整代码)
END;</code>

3. Sample interval generation:

To generate a list of dates between 2009-01-01 and 2009-01-10, execute the following command:

<code class="language-sql">call make_intervals('2009-01-01 00:00:00', '2009-01-10 00:00:00', 1, 'DAY');</code>

4. Connect with your data:

Join the generated date range (located in the time_intervals temporary table) with your data table to aggregate the data as needed.

Example data scenario:

Similarly, you can create a function for SQL Server. This function produces results similar to those mentioned in the question:

<code class="language-sql">CREATE FUNCTION GetDateList(@StartDate DATETIME, @EndDate DATETIME, @Interval INT, @Unit VARCHAR(10))
RETURNS TABLE
AS
BEGIN
    DECLARE @ThisDate DATETIME = @StartDate;
    WHILE @ThisDate <= @EndDate
    -- ... (函数代码，请参考答案中的完整代码)
END;</code>

The above is the detailed content of How to Generate a List of Dates Between Two Dates in MySQL and SQL Server?. For more information, please follow other related articles on the PHP Chinese website!