How to insert all the dates in a date range into Oracle database?

Question

For instance, The date range is from 26th Jul to 1st Aug, that is

26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug

How can I construct a function (of course, I need to implement this function using SQL or PL/SQL) like this:

// insert all the dates from "from" to "to" ("from" and "to" are included)
// into Oracle datebase
bool InsertDateRange(date from, date to) {
 bool state = true;
 for (date d in [from, to]) {
 // insert
 }
 return state;
}

If given a different date range, say [27th Feb, 1st Mar], I also need to know this year is a leap year or a common year.

• mustaccio: Do you need to insert dates, or do you need to write a function to insert dates? These are different things. Also, I don't recognize the language you're using -- it looks similar to Java, but data types are unfamiliar to me. If you want a function, what language are you planning to use?
• yanpengl: Sorry, I cannot describe this question clearly. I need SQL (maybe PL/SQL) to implement this function.

Answer 1

You can use a hierarchical query to generate a sequence of dates, something like this:

insert into my_table (my_date_column) 
select dt + (level - 1) 
from (select date '<your start date>' dt from dual)
connect by dt + (level - 1) <= date '<your end date plus 1 day>';

Date arithmetics: dt + (level - 1) -- are smart enough to know when one month ends and another begins (including Februaries in leap years).

If you do need a PL/SQL function for this, I'm sure you will be able to wrap it around this statement easily.