Dates In Oracle: Ranges, Manipulation & Loops

Dates In Oracle: Ranges, Manipulation & Loops

Having spent a lot of time looking at dates in SQL Server, I thought I’d take some time to publish some notes on dates in Oracle. Most of the ideas are the same, but there are some subtleties.

After my SQL Server articles:

Creating date ranges

As with SQL Server, a quick way to create dates is to use a large table, or virtual table and do a calculation from the rows, it’s worth reading about Oracle Row Generator Techniques. However, the most common and simple methods use one of:

  1. A number sequence.
  2. CONNECT BY and dual.

I’ve already covered using a number sequence in detail (see Dates In SQL Server: Iteration of Date Ranges), so I’ll just link to the example: Create Date list – Oracle

The other method, using dual and CONNECT BY creates a virtual dataset with a variable number of rows.

WITH date_list AS (
SELECT TRUNC(SYSDATE) - rownum date_val -- Calculation of start date and variance
  FROM dual
    CONNECT BY LEVEL <= 366             -- How many rows to create in the dataset
)
SELECT date_val
  FROM date_list
 WHERE date_val BETWEEN SYSDATE - 100   -- Put our required date range here
                    AND SYSDATE         -- Put our required date range here
 ORDER BY date_val
;

Formatting dates

Some things I wish were in SQL Server which are in oracle:

  • TO_DATE()
  • TO_CHAR()
  • TRUNC()
SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'yyyy-mm-dd')      AS sysdate_format_1, -- ISO short format
       TO_CHAR(SYSDATE, 'mon-dd-yy')       AS sysdate_format_2, -- Crazy format not in SQL Server
       TO_DATE('2018-03-17', 'yyyy-mm-dd') AS to_date_format_1, -- ISO short format
       TO_DATE('jan-17-18',  'mon-dd-yy')  AS to_date_format_2, -- Crazy format not in SQL Server
     --Round to start of day, month or year:
       TRUNC(SYSDATE)                      AS sysdate_day,
       TRUNC(SYSDATE, 'MM')                AS sysdate_month,
       TRUNC(SYSDATE, 'YY')                AS sysdate_year
 FROM dual
;

Looping over dates

Date Incrementing

This is the basic method of iterating from a start date to an end date, without having to create a dataset. This is quick and easy to do, and the code is easy to read. I use these techniques for ad-hoc processing.

These templates allow you to copy/paste them. Change the dates at the top, and the contents between “Start Work” and “End Work”.

V1 WHILE Loop

SET SERVEROUTPUT ON;

--------------------------------------------------------------------------------
-- Loop
--------------------------------------------------------------------------------
DECLARE
  start_date  DATE := TO_DATE('2015-12-08', 'yyyy-mm-dd');
  end_date    DATE := TO_DATE('2016-01-01', 'yyyy-mm-dd') - 1;
  active_date DATE;
BEGIN
  dbms_output.put_line('Date Start: ' || TO_CHAR(start_date, 'YYYY-MM-DD'));
  dbms_output.put_line('  Date End: ' || TO_CHAR(end_date,   'YYYY-MM-DD'));
  
  ------------------------------------------------------------------------------
  -- Check Date Range
  ------------------------------------------------------------------------------
  IF start_date > end_date THEN
  BEGIN
    RAISE_APPLICATION_ERROR(-20999, 'Invalid Date Range: start_date AFTER end_date');
  END;
  END IF;
  
  ------------------------------------------------------------------------------
  -- Loop Days 
  ------------------------------------------------------------------------------
  active_date := start_date;
  WHILE active_date <= end_date
  LOOP
    dbms_output.put_line('Date Is: ' || TO_CHAR(active_date, 'YYYY-MM-DD'));
    ----------------------------------------------------------------------------
    -- Start Work
    ----------------------------------------------------------------------------
    
    /*
    schema_name.proc_name(active_date);
    */
    
    ----------------------------------------------------------------------------
    -- End Work / Increment Date
    ----------------------------------------------------------------------------
    active_date := TRUNC(active_date) + 1;
  END LOOP;
END;

See example in action: Oracle – Date Loop Sample (incrementing V1)

V2 FOR Loop

This takes advantage of converting the date to a straight number using Julian day (the number of days since January 1, 4712 BC). It has the advantage of not having to worry about incrementing the date.

SET SERVEROUTPUT ON;

--------------------------------------------------------------------------------
-- Loop
--------------------------------------------------------------------------------
DECLARE
  start_date  DATE := TO_DATE('2015-12-08', 'yyyy-mm-dd');
  end_date    DATE := TO_DATE('2016-01-01', 'yyyy-mm-dd') - 1;
  active_date DATE;
  
  start_number NUMBER;
  end_number   NUMBER;
BEGIN
  dbms_output.put_line('Date Start: ' || TO_CHAR(start_date, 'YYYY-MM-DD'));
  dbms_output.put_line('  Date End: ' || TO_CHAR(end_date,   'YYYY-MM-DD'));
  
  ------------------------------------------------------------------------------
  -- Check Date Range
  ------------------------------------------------------------------------------
  IF start_date > end_date THEN
  BEGIN
    RAISE_APPLICATION_ERROR(-20999, 'Invalid Date Range: start_date AFTER end_date');
  END;
  END IF;
  
  ------------------------------------------------------------------------------
  -- Loop Days 
  ------------------------------------------------------------------------------
  start_number := TO_NUMBER(TO_CHAR(start_date, 'j'));
  end_number   := TO_NUMBER(TO_CHAR(end_date,   'j'));
  
  FOR cur_r IN start_number..end_number
  LOOP
  --dbms_output.put_line('Cur Is: ' || TO_CHAR(cur_r));
    active_date := TO_DATE(cur_r, 'j');
    
    dbms_output.put_line('Date Is: ' || TO_CHAR(active_date, 'YYYY-MM-DD'));
    ----------------------------------------------------------------------------
    -- Start Work
    ----------------------------------------------------------------------------
    
    /*
    schema_name.proc_name(active_date);
    */
    
    ----------------------------------------------------------------------------
    -- End Work
    ----------------------------------------------------------------------------
  END LOOP;
END;

See example in action: Oracle – Date Loop Sample (incrementing V2)

Cursor

The more oracle pure way of iterating dates is using a cursor, for straight date ranges this will have little advantage over the iteration techniques. This method comes into its own if the dates require some logic (skipping weekends, dates already in another list).

This method is also easy to apply for iterating over any data set of any datatype (a collection of VARCHAR2 values can’t be incremented).

-- On by default at http://rextester.com/
--SET SERVEROUTPUT ON;

--------------------------------------------------------------------------------
-- Loop
--------------------------------------------------------------------------------
DECLARE
  active_date DATE;
  
  CURSOR date_cursor1
  IS
  WITH date_list AS (
  SELECT TRUNC(SYSDATE) - rownum date_val -- Calculation of start date and variance
    FROM dual
      CONNECT BY LEVEL <= 1000            -- How many rows to create in the dataset
  )
  SELECT date_val
    FROM date_list
   WHERE date_val BETWEEN TO_DATE('2015-12-08', 'yyyy-mm-dd')     -- Put our required date range here
                      AND TO_DATE('2016-01-01', 'yyyy-mm-dd') - 1 -- Put our required date range here
   ORDER BY date_val
  ;

BEGIN
  ------------------------------------------------------------------------------
  -- Loop Days 
  ------------------------------------------------------------------------------
  OPEN date_cursor1;
  LOOP
    FETCH date_cursor1 INTO active_date;
    EXIT WHEN date_cursor1%NOTFOUND;
    
    dbms_output.put_line('Date Is: ' || TO_CHAR(active_date,   'YYYY-MM-DD'));
    ----------------------------------------------------------------------------
    -- Do Work
    ----------------------------------------------------------------------------
    
    /*
    schema_name.proc_name(active_date);
    */
    
    ----------------------------------------------------------------------------
    -- End Work
    ----------------------------------------------------------------------------
  END LOOP;
  CLOSE date_cursor1;
END;

See example in action: Oracle – Date Loop Sample (cursor)

Note: For some processes, one can use a SYS_REFCURSOR as part of the loop. This allows one function to return the cursor values so they don’t have to be available at runtime of the procedure which eventually calls/iterates the cursor.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.