How Can I Generate a Date Range in SQL?

Generating a Date Range Using SQL

When building SQL queries, it's often necessary to work with a range of dates. To cater to this need, SQL includes functionality for generating sequences of dates. Let's explore how to generate a range of dates to aid in your query execution.

Generating a Sequence of Days

One common scenario is generating a sequence of dates representing the last year. To achieve this using pure SQL, you can employ a query like the following:

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

This query utilizes Oracle's DUAL table, a table with a single row and one column, which acts as a placeholder for generating the sequence. The CONNECT BY clause enables recursive query execution, creating a sequence of days by decrementing SYSDATE (the current system date). The ROWNUM pseudo-column provides the iteration count, and specifying < 366 generates a range of 365 days (plus an extra day to account for leap years).

Alternatively, you can generate day offsets instead of full dates and apply them to your date parameter:

SELECT SYSDATE - val FROM (SELECT 0 + LEVEL - 1 val FROM DUAL CONNECT BY LEVEL < 366)

This query uses the LEVEL pseudo-column to generate a sequence of day offsets, which can be combined with SYSDATE to produce a range of dates.

Remember to consider your database's specific syntax and features when implementing these solutions to ensure compatibility and efficiency.

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