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:
- Dates In SQL Server: Managing & Manipulating Dates
- Dates In SQL Server: Create Sample Date Ranges
- Dates In SQL Server: Iteration of Date Ranges
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:
- A number sequence.
- 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.