X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • June 1, 2021

How to generate days, weeks, or months between two dates in Oracle Database

Chris Saxon
Developer Advocate

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:

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 numbers of days from this, 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_months
  • months_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 mm format of trunc to return the first of the month
  • End date: Use lead to 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 lead returns 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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.