Oracle Live SQL - Script: Generating days, months or years between dates (2023)

The connect by level trick enables you to create N rows on-the-fly

Generate rows

select level as N from dual connect by level <= 10

Using this method, you can create days by adding the level (row number) to a starting date

Generate days

select date'2021-01-01' + level - 1 as dt from dual connect by level <= 31

You can use the generation trick to report number of orders per day - including days with no orders.This finds the dates of the first and last orders, then uses these to generate the days. Note the dates subquery uses RWS as its source table.

Show orders per day

with rws as ( select min ( order_datetime ) mn_dt, max ( order_datetime ) mx_dt from co.orders o ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= ( extract ( day from ( mx_dt - mn_dt ) ) + 1 ) ) select dt, count(order_datetime) from dates left join co.orders on dt <= order_datetime and dt + 1 > order_datetime group by dt order by dt

You can use the method above to find orders per day for a given customer, store, or product. The initial subquery gets the rows and columns needed to do this

Show orders per day for a store

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 ) 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

This generates all the days in the year, then filters these down to the Fridays. This means the query processes ~7x more rows than needed.

Find the Fridays in 2021

with rws as ( select date'2021-01-01' + level - 1 as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Friday'

This calculates how many Fridays there are in the year, then only generates that many rows.This uses next_day to find the first Friday of the year. This returns the Friday after the input date, so you need to start from 31st Dec the previous year in case 1st Jan is a Friday.

Find all the Fridays - improved

with rws as ( select next_day ( date'2021-01-01' - 1, 'Friday' ) + ( level - 1 ) * 7 as dt from dual connect by level <= ( ( date'2021-12-31' - next_day ( date'2021-01-01' - 1, 'Friday' ) + 7 ) / 7 ) ) select dt from rws

Set session language to Spanish

alter session set nls_language = Spanish

Next_day is sensitive to NLS_LANGUAGE settings. This fails because the session now uses Spanish, so expects Viernes.The function has no parameter to override this

Language settings for next_day

select next_day ( date'2021-01-01', 'Friday' ) from dual

Set session language back to English

alter session set nls_language = English

Now using Spanish days-of-the-week fails.

Language settings for next_day

select next_day ( date'2021-01-01', 'Viernes' ) from dual

The number of days in a month changes, so to get a row per month you need a different method.Months_between returns the number of these from the first date to the second. This returns part-months as a decimal, calculated using a 31-day month. Add_months increments the date by N months, returning the same day-of-month as the initial date, unless the date is the last day of the month. In which case the calculated date is also the last day of the month.

Generate months

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

select add_months ( date'2021-01-15', level - 1 ) as dt from dual connect by level <= months_between ( date'2022-01-15', date'2021-01-15' ) + 1

This shows the first and last date per month in the time period, beginning on the start date. The final date is the same day of the month as the input (or last day of the month if the input is the last).

Generate month start and end dates

with mths as ( select add_months ( date'2021-01-15', level - 1 ) as dt from dual connect by level <= months_between ( date'2022-01-15', date'2021-01-15' ) + 1 ) select case rownum 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

This uses intervals to add the number of years to the input date.

Generate years

select date'2021-01-01' + numtoyminterval ( level - 1, 'year' ) as dt from dual connect by level <= ( months_between ( date'2022-12-31', date'2020-01-01' ) / 12 ) + 1

Make these reusable with SQL macros

create or replace package date_mgr as function generate_days ( start_date date, end_date date, day_increment integer default 1 ) return varchar2 sql_macro; function generate_months ( start_date date, end_date date, month_increment integer default 1 ) return varchar2 sql_macro; function generate_years ( start_date date, end_date date, year_increment integer default 1 ) return varchar2 sql_macro; end date_mgr; 

Make these reusable with SQL macros

create or replace package body date_mgr as function generate_days ( start_date date, end_date date, day_increment integer default 1 ) return varchar2 sql_macro as stmt varchar2(4000); begin stmt := 'select start_date + ( level - 1 ) * day_increment as dt from dual connect by level <= ( ( ( end_date - start_date ) + day_increment ) / day_increment )'; dbms_output.put_line ( stmt ); return stmt; end generate_days; function generate_months ( start_date date, end_date date, month_increment integer default 1 ) return varchar2 sql_macro as stmt varchar2(4000); begin stmt := ' select add_months ( start_date, ( level - 1 ) * month_increment ) as dt from dual connect by level <= ( months_between ( end_date, start_date ) + month_increment ) / month_increment'; dbms_output.put_line ( stmt ); return stmt; end generate_months; function generate_years ( start_date date, end_date date, year_increment integer default 1 ) return varchar2 sql_macro as stmt varchar2(4000); begin stmt := q'! select start_date + numtoyminterval ( ( level - 1 ) * year_increment, 'year' ) as dt from dual connect by level <= ( ( months_between ( end_date, start_date ) / 12 ) + year_increment ) / year_increment!'; dbms_output.put_line ( stmt ); return stmt; end generate_years; end date_mgr; 

Generate days using SQL macro

select * from date_mgr.generate_days ( date'2021-01-01', date'2021-01-31' )

Generate every Friday using SQL macro

select * from date_mgr.generate_days ( next_day ( date'2021-01-01' - 1, 'Friday' ), date'2021-12-31', 7 )

Generate every other Monday

select * from date_mgr.generate_days ( next_day ( date'2021-01-01' - 1, 'Monday' ), date'2021-12-31', 14 )

Generate months using SQL macro

select * from date_mgr.generate_months ( next_day ( date'2021-01-01' - 1, 'Monday' ), date'2021-12-31' )

Using the SQL macro, you can create a row per quarter by setting the increment to 3.

Generate quarters

select * from date_mgr.generate_months ( date'2021-01-01', date'2021-12-31', 3 )

Generate three years with SQL macros

select * from date_mgr.generate_years ( date'2020-01-01', date'2022-12-31' )

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 ) from ( select * from rws cross join date_mgr.generate_days ( mn_dt, mx_dt ) ) left join stor on dt <= order_datetime and dt + 1 > order_datetime group by dt order by dt

Generating dates is a handy trick, but storing the dates in a table makes queries easier to write. It also allows you to business information about dates, such as whether they are working days.You can also add columns for date attributes you want to filter on, such as day-of-week, month-of-year, etc.

Create a dates table

create table calendar_dates ( calendar_date date check ( calendar_date = trunc ( calendar_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char) ) organization index

This loads the dates table with a row per day, using the SQL macro. While doing so it sets Saturday and Sunday to be non-working days

Store days of the year

insert into calendar_dates ( calendar_date, is_working_day, day_of_week ) select dt, case when to_char ( dt, 'dy', 'nls_date_language = english' ) in ( 'sat', 'sun' ) then 0 else 1 end, to_char ( dt, 'FMDay', 'nls_date_language = english' ) from date_mgr.generate_days ( date'2021-01-01', date'2021-12-31', 1 )

Set UK public holidays to be non-working days

update calendar_dates set is_working_day = 0 where calendar_date in ( date'2021-01-01', date'2021-04-02', date'2021-04-05', date'2021-05-03', date'2021-05-31', date'2021-08-30', date'2021-12-27', date'2021-12-28' )

commit

Find all the Mondays in the year

select calendar_date from calendar_dates where day_of_week = 'Monday' and calendar_date between date'2021-01-01' and date'2021-12-31'

With the data stored in a table, it's easy to write queries to find working days.

Find working Mondays in year

select calendar_date from calendar_dates where day_of_week = 'Monday' and calendar_date between date'2021-01-01' and date'2021-12-31' and is_working_day = 1