How to Efficiently Generate a 365-Day Date Range in SQL?

Generating a Date Range using SQL

To generate a range of dates for SQL execution, a common requirement is to iterate over a specific period. In this scenario, the objective is to create a list of the previous 365 days.

Conventionally, creating a list of days using numbers (e.g., 0 to 364) is a viable solution. However, using a more efficient approach eliminates the need for large tables or additional operations.

The optimal SQL query to generate a date range for the last 365 days is:

SELECT TRUNC (SYSDATE - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 366

This query leverages the TRUNC function to determine the dates for each row, starting from the current date (SYSDATE) and subtracting the incremental ROWNUM value. The CONNECT BY clause executes the query iteratively for as many rows as specified in the conditional (ROWNUM < 366 in this case).

The result is a list of dates representing the previous 365 days. This list can be utilized in subqueries or joins to retrieve data for the specified time period.

The above is the detailed content of How to Efficiently Generate a 365-Day Date Range in SQL?. For more information, please follow other related articles on the PHP Chinese website!