Monday, February 06, 2012

How To: List all Dates for sysdate +/- 30 in Oracle

Discovered an answer to a StackOverflow question I'd posted, and wanted to share.

I needed to, within a view, dynamically generate a list of dates before or after sysdate. I wanted to do this so that I could limit some of the records coming back (we have millions but the date range will always be between 31 days before sysdate or 31 says after.

In Oracle, the SQL to do this is as follows:

SELECT *
FROM   (SELECT TRUNC(SYSDATE - ROWNUM) DateItem
        FROM   DUAL
        CONNECT BY ROWNUM < 32
        UNION
        SELECT TRUNC(SYSDATE + ROWNUM) DateItem
        FROM   DUAL
        CONNECT BY ROWNUM < 32)DATERANGE; 
This SQL does the following:

  • Uses the dual table (a special table in Oracle as I understand) to scroll through x amount of rows (in this case, 32, because we want to make sure it includes 31 days)
  • For each of those, it returns the sysdate - the number of rows, or sysdate + the number of rows in the case of the second query. It calls this column "DT".
  • It unions those two queries together, to give the full range of values. This also has the nice effect of removing duplicate dates from the results.
  • I then wrap it in a select * statement so I can call it what I want and refer to it later.
Learning Oracle as I go, and I'm enjoying discovering these little tricks.

No comments:

Post a Comment

Keep it classy, folks.