How to Generate a Date Range Covering the Last Year in SQL?

Generating Date Ranges Using SQL

Question:

How can I generate a list of dates spanning the last year for use in a SQL query that requires a date parameter?

Answer:

To generate a range of dates using SQL, there are various methods available. One efficient approach is to utilize the CONNECT BY clause, as demonstrated below:

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

This query generates a list of 366 dates, representing the last 365 days. The TRUNC function truncates the date to remove the time component, leaving only the date value.

By using the DUAL table in conjunction with CONNECT BY, you can generate a sequence of rows based on the specified condition (ROWNUM < 366 in this case). Each row represents a single date within the specified range.

This method provides a straightforward and efficient solution for generating a range of dates for use in SQL queries, without the need for additional tables or complex logic.

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