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:
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
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:
NOTE The display of datetime values is determined by your client's NLS settings. This post uses these settings to format
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.
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 numbers of days from this, use
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.
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.
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
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
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?
Say you need to show every Monday in a year. One way to do this is to:
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.
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:
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:
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:
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
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
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.
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
truncto 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.
Oracle Database includes these month arithmetic functions:
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
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.
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.
|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:
To do these in SQL is straightforward:
truncto return the first of the month
leadto find the month for the next row. As with the start date, use
trunc ( … , '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 for
lead. This is the value
leadreturns when trying to get a value beyond the end of the result set.
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.
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:
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
The process for creating a row per year is like that outlined for quarters:
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.
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:
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.
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
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:
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.