Use TRUNC function to generate various date ranges

How to use TRUNC function to generate a date range? As it will be demonstrated below, it is very straightforward and simple to grasp. Let’s start from the very beginning

Step 1. Generate Numeric Range

First, you need to understand how to generate a numeric range. Let say, you need to generate a range of integers from 1 to 10. There are 2-3 traditional ways to do it.

Method 1: Use Connect By clause:

SQL> SELECT LEVEL, ROWNUM
  2  FROM dual
  3  CONNECT BY LEVEL<=10;

     LEVEL     ROWNUM
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

Likewise, you can use ROWNUM in the CONNECT BY:

SQL> SELECT LEVEL, ROWNUM
  2  FROM dual
  3  CONNECT BY ROWNUM<=10;

     LEVEL     ROWNUM
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

Method 2: Using some data dictionary table that is always available:

SQL> SELECT ROWNUM
  2  FROM all_objects
  3  WHERE ROWNUM<=10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

If the table does not have enough rows, you can use a Cartesian Product (emp table only has 14 rows):

SQL> SELECT ROWNUM
  2  FROM emp, emp
  3  WHERE ROWNUM<=16;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16

The above method involves disk I/O which makes it fairly inefficient compare to the CONNECT BY method.

Method 3: Using Recursive WITH clause:

SQL> WITH x(rnum) AS (
  2  SELECT 1 AS rnum
  3  FROM dual
  4  UNION ALL
  5  SELECT rnum+1
  6  FROM x
  7  WHERE rnum

This method first became available in Oracle 11.2 when Oracle introduced support to the Recursive WITH clause. The good thing about this method is that it is often available in other RDBMS (SQL Server, Teradata, etc.) that don’t have a support for CONNECT BY.

Step 2. Convert the Numeric range from Step 1 into a Date Range.
This step is very simple since we know that we can easily add days to a specific date value. For simplicity, we will stick with CONNECT BY method of numeric range generation:

SQL> SELECT SYSDATE + LEVEL - 1 AS day
  2  FROM dual
  3  CONNECT BY LEVEL<=10;

DAY
---------
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16

Now, we will want to generate very specific data ranges.

Problem: Generate the date range for current week from Sunday to Saturday.

All we need to know is how to get the first day of the week. We have explained this in details in a previous post:

SQL> SELECT TRUNC(SYSDATE, 'DAY') week_start
  2  FROM dual;

WEEK_STAR
---------
06-MAR-16

Now, we will generate the range for the week knowing that the week has 7 days:

SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY LEVEL<=7;

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16

Do we really need to know how many days our desired date range has? The answer is NO. All we need is to ensure that every subsequent day remains in the same date interval (same week – in our case). How can we identify the week – by its first day!

SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TRUNC(TRUNC(SYSDATE, 'DAY')+LEVEL-1, 'DAY')=TRUNC(SYSDATE, 'DAY')
  4  /

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16

As long as subsequent day’s first day of the week remains the same as the current day’s first day of the week, we can continue the recursion!

Using this idea, generation of the month’s date range is even simpler as we can use either TRUNC function to get the first day of the month, or TO_CHAR(…, ‘MM’) – to extract the month:

SQL> SELECT TRUNC(SYSDATE, 'MON')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'MON')+LEVEL-1, 'MM')=TO_CHAR(SYSDATE, 'MM')
  4  /

DAY
---------
01-MAR-16
02-MAR-16
03-MAR-16
...
30-MAR-16
31-MAR-16

How about getting the date range for the current Quarter?

SQL> SELECT TRUNC(SYSDATE, 'Q')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'Q')+LEVEL-1, 'Q')=TO_CHAR(SYSDATE, 'Q')
  4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-MAR-16
31-MAR-16

A Year?

SQL> SELECT TRUNC(SYSDATE, 'YY')+LEVEL-1 AS day
  2  FROM dual
  3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'YY')+LEVEL-1, 'YY')=TO_CHAR(SYSDATE, 'YY')
  4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-DEC-16
31-DEC-16

As you can see, the solution is very simple!

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

2 thoughts on “Use TRUNC function to generate various date ranges

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s