There are many tricks to generate rows in Oracle Database. The easiest is the connect by level method:
select level rn from dual
connect by level <= 3;
RN
1
2
3
You can use this to fetch all the days between two dates by:
- Subtracting the first date from the last to get the number of days
- Generate this many rows (adding one if you want to include the end date in the output)
- Add the current row number (minus one) to the start date
To create a row for each day from 8th-14th June inclusive, use:
select date'2021-06-08' + level - 1 dt from dual connect by level <= ( date'2021-06-14' - date'2021-06-08' + 1 ); DT 08-Jun-2021 09-Jun-2021 10-Jun-2021 11-Jun-2021 12-Jun-2021 13-Jun-2021 14-Jun-2021
Easy, right?
But this only works if you want every day between two dates. If you’re working with timestamps or want a different period between each row (e.g. weeks, months or years) you need to tweak this query.
In this post you’ll learn how to:
- Get the days between
timestamps - Generate rows from the oldest to the newest date in a table
- Return every Monday in a year
- Fetch every second Friday in a year
- Get the months between two dates
- Show the first and last day of each month
- Return a row per quarter
- Generate a row per year in a period
- Make reusable date generation functions
NOTE The display of datetime values is determined by your client’s NLS settings. This post uses these settings to format date and timestamp values
alter session set nls_date_format = 'DD-Mon-YYYY'; alter session set nls_timestamp_format = 'DD-Mon-YYYY hh24:mi:ss';
To ensure you always display dates in the same way, use to_char to set their format.
If you prefer your content in video form, you can watch the recording of the Ask TOM Office Hours session where I covered this topic in detail:
You can get the scripts used in the recording on Live SQL. These are similar to the examples in this post.
How to get the number of days between timestamp values
When you subtract one date value from another, the result is a number. The unit is the number of days.
But if either of the values are timestamps, the difference will be a (day to second) interval. To get the number of days between timestamps, use extract:
with time_between as (
select timestamp'2021-06-14 06:00:00' -
timestamp'2021-06-08 14:00:00' t
from dual
)
select t, extract ( day from t ) dys
from time_between;
T DYS
+05 16:00:00.000000 5
This example highlights another potential issue.
The start time is later in the day than the end time, so the time between the dates is part of a day. If you only add complete days, you can end up one day short. Using these values to create the rows, even after adding one to the extracted days, you’ll only get 8th-13th June in the results:
with time_between as (
select timestamp'2021-06-14 06:00:00' -
timestamp'2021-06-08 14:00:00' t
from dual
)
select timestamp'2021-06-08 14:00:00'
+ numtodsinterval ( level - 1, 'day' ) dt
from time_between
connect by level <= extract ( day from t ) + 1;
DT
08-Jun-2021 14:00:00
09-Jun-2021 14:00:00
10-Jun-2021 14:00:00
11-Jun-2021 14:00:00
12-Jun-2021 14:00:00
13-Jun-2021 14:00:00
6 rows selected.
As dates in Oracle Database always include a time, this issue can crop up for any input values. The connect by level method ignores fractional digits. So you need to round up the difference.
When working with other periods, such as weeks, months, or years you need to round up to this unit.
Whatever units you use, double-check whether you should include or exclude the end value to avoid off-by-one errors.
When creating a row per day, you can avoid part-day issues by trunc()ing the values first. This strips off the time, returning a date with a time of midnight. The result is always a date, even if the input is a timestamp. So instead of extract, you can trunc the input values to get all the days in the range:
with time_between as (
select trunc ( timestamp'2021-06-14 06:00:00' ) -
trunc ( timestamp'2021-06-08 14:00:00' ) t
from dual
)
select *
from time_between;
T
6
This is particularly useful if you want to generate the date range using table columns. For example, to show every day between two rows in a table.
How to get the days between the oldest and newest in a table
So far we’ve assumed the start and end dates will be user-defined. For example, show every day in January, the past four weeks or between two dates supplied by an end-user.
But sometimes you want to use the first and last insert dates in a table as the start and end points. To do this, get these values and use them to drive the row generator.
This example uses the Customer Orders schema to find the oldest and newest dates in the orders table:
select min ( order_datetime ) start_date,
max ( order_datetime ) end_date
from co.orders;
START_DATE END_DATE
04-Feb-2018 13:20:22 12-Apr-2019 21:41:06
The connect by level trick works on any one-row table. And the query above returns one row! So you can use this as the data source to create rows:
with ranges as (
select min ( order_datetime ) start_date,
max ( order_datetime ) end_date
from co.orders
)
select start_date + level - 1 dt
from ranges
connect by level <= (
trunc ( end_date ) - trunc ( start_date ) + 1
);
DT
04-Feb-2018
05-Feb-2018
...
11-Apr-2019
12-Apr-2019
433 rows selected.
You can use any query to define the date range. So you can use this method to return dates for a subset of a table. For example, showing all the days between the first and last order for a given store or customer:
with stor as (
select store_id, order_datetime
from co.orders o
where store_id = 23
), rws as (
select min ( trunc ( order_datetime ) ) mn_dt,
max ( trunc ( order_datetime ) ) mx_dt
from stor
)
select dt, count ( order_datetime ) num_orders
from (
select mn_dt + level - 1 as dt
from rws
connect by level <= mx_dt - mn_dt + 1
)
left join stor
on dt <= order_datetime
and dt + 1 > order_datetime
group by dt
order by dt;
DT NUM_ORDERS
22-Mar-2019 1
23-Mar-2019 0
...
11-Apr-2019 3
12-Apr-2019 1
22 rows selected.
So far we’ve been creating a row for every day. But what if you want to use a different time unit, such as a row per week?
How to display every Monday in a year
Say you need to show every Monday in a year. One way to do this is to:
- Generate a row for every day in the year
- Filter the output to only show the day you want
The to_char function has various format masks you can use to extract the day-of-week from a date. This uses day to get the full day name:
with rws as (
select date'2020-12-31' + level dt
from dual
connect by level <= (
date'2022-01-01' - date'2021-01-01'
)
)
select dt
from rws
where to_char (
dt,
'fmday',
'nls_date_language = English'
) = 'monday';
DT
04-Jan-2021
11-Jan-2021
...
20-Dec-2021
27-Dec-2021
52 rows selected.
Specifying the nls_date_language is good practice. This ensures the conversion always uses English names for days of the week, regardless of your session’s language settings.
But there is an issue with this query: it creates roughly 7x more rows than you need. This breaks a key principle for fast SQL:
Filter early!
Only selecting the rows you need minimizes data transfer. The less data you process, the faster your queries will be.
In practice you’re unlikely to notice the difference here. But only getting the data you want is a good habit to get into. It’s better to only generate a row for each week in the output.
The number of Mondays in a year varies between 52 and 53. This depends on which day-of-week the 1st January is on and whether it’s a leap year.
So how many rows do we want?
This formula returns the number of Mondays in a year:
31st December - first Monday |
+ 1 week |
7 days |
There’s no time unit of a week in Oracle Database. To simulate this, use seven days.
To find the first Monday of the year, you could either:
- Use
next_day; this returns the first date of the requested day-of-week after the input. If the input date is on the requested day, it returns the next week. So to find the first Monday of the year pass 31 Dec of the previous year to this - Find the start of the ISO week for the 7th January of the input year
So these both find the first Monday in 2021:
select next_day ( date'2020-12-31', 'Monday' ) nxt_day,
trunc ( date'2021-01-07', 'iw' ) trnc
from dual;
NXT_DAY TRNC
04-Jan-2021 04-Jan-2021
Using next_day is more intuitive. Everyone looking at the code will understand your intent.
But it has a big drawback.
It uses the NLS_LANGUAGE to determine day names. This is a session modifiable setting. If there’s a mismatch between this and the language you use for day names, you’ll get an error:
alter session set nls_language = Spanish; select next_day ( date'2020-12-31', 'Monday' ) nxt_day from dual; ORA-01846: día de la semana no válido
Sadly there’s no parameter to specify which NLS_LANGUAGE to use in next_day. So you have to check and set this in your code to avoid unexpected errors.
Using the start of the ISO week avoids this problem. This is always a Monday. So the query will always give the same result, even if someone changes the NLS settings.
Whichever method you use to find the first Monday, swap a week for seven days in the formula above, giving:
alter session set nls_language = English;
with rws as (
select next_day ( date'2021-01-01' - 1, 'Monday' )
+ ( level - 1 ) * 7 as dt
from dual
connect by level <= (
( date'2021-12-31' -
next_day ( date'2021-01-01' - 1, 'Monday' ) +
7
) / 7
)
)
select dt from rws;
DT
04-Jan-2021
11-Jan-2021
...
20-Dec-2021
27-Dec-2021
52 rows selected.
This protects your code from NLS changes. But it is a little harder to understand, particularly if you want to return a different day-of-week.
How to get every other Friday
You can generalize the solution to get every Monday to any day of the week and increment by any number of weeks. For example, every second Friday in 2021.
If you’re using next_day to get the day-of-week, the solution’s easy. Subtract a day from the input value and return the next Friday.
The ISO week method to find the first Friday on or after the input is trickier. The formula for this is:
date + 6 - mod ( ( date + 6 - day of week number ) - start of ISO week, 7 )
The day numbers are 0-6, with zero being Monday and six Sunday.
To get every second week, normalize the days by 14 to get a row per fortnight.
Here’s an example using both next_day and trunc to select every other Friday (day 4) in 2021:
with input as (
select date'2021-01-01' start_date,
date'2021-12-31' end_date,
4 day_of_week
from dual
), rws as (
select next_day ( start_date - 1, 'Friday' )
+ ( level - 1 ) * 14 as nxt_day,
start_date + 6 - mod (
( start_date + 6 - day_of_week )
- trunc ( start_date , 'iw' ),
7
)
+ ( level - 1 ) * 14 as trnc
from input
connect by level <= (
( end_date - next_day ( start_date - 1, 'Friday' )
+ 14
) / 14
)
)
select * from rws;
NXT_DAY TRNC
01-Jan-2021 01-Jan-2021
15-Jan-2021 15-Jan-2021
...
17-Dec-2021 17-Dec-2021
31-Dec-2021 31-Dec-2021
27 rows selected.
While the ISO week method will return the same result whatever your NLS settings, it’s harder to understand than next_day. And it relies on those calling to know it assumes Monday is the first day of the week with a value of zero.
Whichever method you use to find the start date, you can use this day-to-week normalization technique to create other day-based intervals. For example, every four weeks or every tenth day.
But what if you want calendar months? These vary between 28 and 31 days. To do this correctly you need to change tack.
How to select the months between two dates
Oracle Database includes these month arithmetic functions:
add_monthsmonths_between
As the names imply these use months as their base unit. So to find how many rows to generate, use months_between the start and end dates. Then increment the month for each row with add_months.
This creates a row for every month in 2021:
select add_months (
date'2021-01-01',
level - 1
) as dt
from dual
connect by level <= months_between (
date'2021-12-31',
date'2021-01-01'
) + 1;
DT
01-Jan-2021
01-Feb-2021
...
01-Nov-2021
01-Dec-2021
12 rows selected.
Add_months uses the day-of-month of the input as the reference point. The returned date is on the same day of the month as this. With one caveat: if the input date is the last day of the month, the output will be on the last of the month too.
Months_between works in a similar way; if the two dates are on the same day-of-month or the last day of that month, the result is an integer. In all other cases this includes the fractional difference between the months, using a 31-day month as its reference.
In most cases this will be what you want. But if you want to run a process on the 28th day of every month, take care!
If you start with January, every row will fall on the 28th:
select add_months (
date'2021-01-28',
level - 1
) as dt
from dual
connect by level <= months_between (
date'2021-03-01',
date'2021-01-01'
) + 1;
DT
28-Jan-2021
28-Feb-2021
28-Mar-2021
But start with February (on non-leap years) and every other row will shift to the end of the month!
select add_months (
date'2021-02-28',
level - 1
) as dt
from dual
connect by level <= months_between (
date'2021-04-01',
date'2021-02-01'
) + 1;
DT
28-Feb-2021
31-Mar-2021
30-Apr-2021
To ensure all the dates fall on the 28th of every month, add month intervals instead:
select date'2021-02-28'
+ numtoyminterval ( level - 1, 'month' ) as dt
from dual
connect by level <= months_between (
date'2021-04-01',
date'2021-02-01'
) + 1;
DT
28-Feb-2021
28-Mar-2021
28-Apr-2021
This works because adding month intervals returns the same day-of-month as the input.
So why did I use add_months to start with?
If the start date is on the 29th or later, eventually the result will map to a non-existent date (e.g. 30th February) and raise an exception:
select date'2021-01-30'
+ interval '1' month as dt
from dual;
ORA-01839: date not valid for month specified
So when adding months think carefully about how you want to handle start dates that are on or after the 28th day of the month. One way to avoid this issue is to show two columns for each month. One for the start and one for the end.
How to get a row per month with the first and last day of each
You may want to extend the row-per-month solution to have two columns showing month start and end dates.
For the first row the start date is the input date, for every other it’s the first of the month. The end date should show the last date in the month, except for the final row which should have the same day-of-month as the first.
For example:
| Start Date | End Date |
| 15 Jan 2021 | 31 Jan 2021 |
| 1 Feb 2021 | 28 Feb 2021 |
| 1 Mar 2021 | 31 Mar 2021 |
| 1 Apr 2021 | 15 Apr 2021 |
To create the rows, use the month generation technique above. Then use these rules to set the column values:
- Start date: for the first row, return the input date; otherwise return the first of the month
- End date: get the month start for the next row and subtract one day from it. For the last row return the calculated date.
To do these in SQL is straightforward:
- Start date: Assign row numbers to the results. If this is one, return the input date. Otherwise use the
mmformat oftruncto return the first of the month - End date: Use
leadto find the month for the next row. As with the start date, usetrunc ( … , 'mm' )to get the first day of this month. Turn this into the last day of the current month by subtracting one. To get the date for the final row, pass the current date as the third parameter forlead. This is the valueleadreturns when trying to get a value beyond the end of the result set.
This gives:
with mths as (
select level as rn,
add_months (
date'2021-01-15',
level - 1
) as dt
from dual
connect by level <= months_between (
date'2021-04-15',
date'2021-01-15'
) + 1
)
select case rn
when 1 then dt
else trunc ( dt, 'mm' )
end start_date,
lead (
trunc ( dt, 'mm' ) - 1,
1, dt
) over (
order by dt
) end_date
from mths;
START_DATE END_DATE
15-Jan-2021 31-Jan-2021
01-Feb-2021 28-Feb-2021
01-Mar-2021 31-Mar-2021
01-Apr-2021 15-Apr-2021
As with the day creation method, you can extend month generation to create other month-based time periods such as quarters and years.
How to fetch a row per quarter in a year
Quarters are three months long. As with weeks, there’s no period defined for these in Oracle Database. So to get a row per quarter, find how many rows you need by:
- Getting the months between the start and end dates
- Divide this by three
- Round the result according to your business rules to include/exclude the end date as needed.
For each row this returns add three months, giving:
select add_months (
date'2021-01-01',
( level - 1 ) * 3
) as dt
from dual
connect by level <= ( months_between (
date'2021-06-01',
date'2021-01-01'
) + 3 ) / 3;
DT
01-Jan-2021
01-Apr-2021
How to return the years in a date range
The process for creating a row per year is like that outlined for quarters:
- Find the number of months between the start and end dates. Divide this by twelve to convert it to years
- For each row add another year to the start date. Do this by multiplying the row number minus one by twelve months or an interval of one year
select add_months (
date'2021-01-01',
( level - 1 ) * 12
) as yr_add_months,
date'2021-01-01' +
numtoyminterval ( level - 1, 'year' ) yr_interval
from dual
connect by level <= ( months_between (
date'2023-01-01',
date'2021-01-01'
) + 12 ) / 12;
YR_ADD_MONTHS YR_INTERVAL
01-Jan-2021 01-Jan-2021
01-Jan-2022 01-Jan-2022
01-Jan-2023 01-Jan-2023
Adding year intervals has the same day-of-month logic as adding month intervals. That is the input and output have the same number.
This leads to a harder trap to spot: leap years. Adding interval '1' year to 29th Feb 2020 leads to the invalid date 29th Feb 2021. So as with generating months, check carefully how you want to handle this and test thoroughly.
At this point we have several similar yet subtly different queries to generate a row per period between two dates.
It’d be nice if you could make these reusable templates. The good news is – starting from 19.6 – you can with SQL macros.
How to make reusable date generation functions with SQL macros
SQL macros return the text of a (scalar or table) SQL expression. At parse time, the database extracts this text and makes it part of the final SQL statement. While doing this, the database searches for the parameter names in the return string. It replaces any it finds with the text of the actual parameters.
For example, to make a day generator macro create a function that:
- Accepts start and end date parameters
- Returns a string which is a table SQL macro
Giving:
create or replace function generate_days (
start_date date, end_date date,
day_increment integer default 1
)
return clob sql_macro as
stmt clob;
begin
stmt := 'select start_date
+ ( level - 1 ) * day_increment as dt
from dual
connect by level <= (
( ( end_date - start_date ) + day_increment
) / day_increment
)';
return stmt;
end generate_days;
You can call this with date literals, bind variables or functions.
For example:
select * from generate_days ( date'2021-06-01', date'2021-06-30' ); DT 01-Jun-2021 02-Jun-2021 ... 29-Jun-2021 30-Jun-2021 30 rows selected. var start_day varchar2(10) exec :start_day := '2021-01-01'; select * from generate_days ( to_date ( :start_day, 'yyyy-mm-dd' ), sysdate, 7 ); DT 01-Jan-2021 08-Jan-2021 ... 14-May-2021 21-May-2021 21 rows selected.
If you want to see the final query, you can get this by passing the call to dbms_utility.get_sql_text:
declare
l_clob clob;
begin
dbms_utility.expand_sql_text (
input_sql_text => q'!select *
from generate_days (
to_date ( :start_day, 'yyyy-mm-dd' ),
sysdate,
7
)!',
output_sql_text => l_clob );
dbms_output.put_line(l_clob);
end;
/
SELECT "A1"."DT" "DT" FROM (
SELECT "A2"."DT" "DT" FROM (
SELECT TO_DATE ( :B1, 'yyyy-mm-dd' ) +
( LEVEL - 1 ) * 7 "DT"
FROM "SYS"."DUAL" "A3"
CONNECT BY LEVEL <= (
SYSDATE - TO_DATE ( :B2, 'yyyy-mm-dd' ) + 7
) / 7
) "A2"
) "A1"
Notice that 7, the bind variable, and sysdate are part of the final SQL statement. The database does not evaluate the variable or function when resolving the macro. They become part of the query as-is.
A sample API to generate days, months or years is available on Live SQL.
That said, if you regularly write queries to generate days, seriously consider creating a dates table. This stores a row for each calendar date you’ll need.
This has several advantages over a generation approach:
- It works even if you’re on an ancient version of Oracle Database
- It’s easier for the optimizer to estimate how many rows you’re fetching, which can lead to better plans and faster queries
- You can add columns to define business-specific periods. For example, the fiscal year or non-working days as such the weekend, public holidays, and temporary business closures.
Summary
The connect by level method is an easy way to generate rows. With this you can return for each day, week, month or year in a date range.
The logic to create rows for each period is subtly different. You need to take particular care when working with dates towards the end of months. And you must watch for off-by-one errors: should results include the end date or exclude it?
You should also ensure any date conversions or calculations are immune to NLS changes. Without doing this calling the code from different server can give you different results!
To handle these issues in a standard way, from Oracle Database 19c, you can use SQL macros to create a date-generation API. But often it’s better still to create a dates table for your application.
If you want learn SQL, join Databases for Developers: Foundations. This free online class will get you started with the basics of SQL.
If you want a fully-fledged database to try these queries on, sign up for an Always Free Autonomous Database on Oracle Cloud Free Tier.
UPDATED 15 Apr 2025: Changed Live SQL links to use new platform