Warm tip: This article is reproduced from stackoverflow.com, please click
database date oracle sql

Interval between two Dates

发布于 2020-04-05 23:42:43

I have a few question about Dates in Oracle.

Is it possible to get the interval (in days) between two DATE types? If yes, is it possible to do a statement (an insert into for example) for each day in this interval?

I thought about 

while (a_sequence != difference_between_dates) 
LOOP 
a_sequence.next 
-- do things
END LOOP;

Is it possible to get that interval with periodic gaps? (Like every week-end for example. Is there a modulo operator in Oracle?) I thought about something like if(a_sequence % 6 || a_sequence % 7) as condition to do things only in week-end for example (assuming the lower date is always Monday).

Is it possible to do my_date+1 to get the next day ?

Questioner
Csi
Viewed
58
分享
Justin Cave 2015-05-25 19:14

Sure. If you're looking for a PL/SQL solution (you can do it in pure SQL but it's probably a bit harder to read), something like

DECLARE
  l_first_date date := date '2015-01-01';
  l_last_date  date := date '2015-12-31';
  l_date_to_check date;
BEGIN
  FOR i IN 1 .. l_last_date - l_first_date
  LOOP
    l_date_to_check := l_first_date + i;
    if( to_char( l_date_to_check, 'DY' ) IN ('SAT', 'SUN') )
    then
      <<do_something>>
    end if;
  END LOOP;
END;

This assumes that your database is using an English language locale (different languages obviously have different abbreviations for days). You can make the code a bit more robust by specifying the NLS settings you want in the to_char function but for most systems, that's adding complexity to the code that is never going to be needed.

Related issues

1
Query is possibly slow?
2
Simple Sql Role query issue
3
Group by query with rollup
4
How to include a PHP variable inside a MySQL statement
5
Getting empty rows from "SELECT ALL" query in PostgreSQL
6
MySQL pivot table using java
7
Implement smart search by text field in database
8
How to speed up count(distinct) with Between clause in MySQL
9
how to perform sql query MIN/MAX FILTER
10
Dynamic pivot in oracle sql