Interval between two Dates

Question

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 ?

Answer 1

CREATE PROCEDURE `sp_calcula_intervalo`(
    IN `p_fl_frequency` VARCHAR(2),
    IN `p_dt_start` VARCHAR(10),
    IN `p_dt_ends` VARCHAR(10),
    OUT `p_qt_ocorrs` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Calculates the interval between two dates'
BEGIN

    -- ------------------------------------------------------------------------------ 
    -- This code was written for Mysql
    -- ------------------------------------------------------------------------------ 
    --   Calculates the interval between two dates
    --   In Weeks, Fortnights, Months, Bimonths, Quarters, Semesters and Years
    --   CALL sp_calcula_intervalo('3M', '2020-01-31', '2020-05-31', @p_qt_ocorrs);
    -- ------------------------------------------------------------------------------

    -- ------------------------------------------------------------------------------
    -- Work Variables
    -- ------------------------------------------------------------------------------

    DECLARE dt_calc        DATE DEFAULT NULL;
    DECLARE dt_start           DATE DEFAULT NULL;
    DECLARE dt_ends              DATE DEFAULT NULL;
    DECLARE dt_proximo_mes      DATE DEFAULT NULL;

    DECLARE va_result        INT            DEFAULT 0;
    DECLARE va_msg              varchar(1000)  DEFAULT '';
    DECLARE va_end_loop              char(1)        DEFAULT 'N';

    DECLARE va_dd_start       char(2)  DEFAULT '00';
    DECLARE va_mm_start       char(2)  DEFAULT '00';

    DECLARE va_dt_next_mm_calc char(10)  DEFAULT '0000-00-00';
    DECLARE va_dd_calc            char(2)  DEFAULT '00';
    DECLARE va_mm_calc            char(2)  DEFAULT '00';

    -- ------------------------------------------------------------------------------
    -- Values Start
    -- ------------------------------------------------------------------------------

   SET va_dd_start = SUBSTR(p_dt_start, 9, 2);
   SET va_mm_start = SUBSTR(p_dt_start, 6, 2);

    SET dt_calc  = STR_TO_DATE(p_dt_start,'%Y-%m-%d'); 
    SET dt_start = STR_TO_DATE(p_dt_start,'%Y-%m-%d'); 
    SET dt_ends  = STR_TO_DATE(p_dt_ends,'%Y-%m-%d');

    SET va_result  = 1;

    -- ------------------------------------------------------------------------------
    -- Loop de Calc
    -- ------------------------------------------------------------------------------

    WHILE (va_end_loop = 'N') DO

        SELECT

            CASE  p_fl_frequency

                   WHEN '1M'  THEN ADDDATE(dt_calc,  INTERVAL 1 MONTH)
                   WHEN '2M'  THEN ADDDATE(dt_calc,  INTERVAL 2 MONTH)
                   WHEN '3M'  THEN ADDDATE(dt_calc,  INTERVAL 3 MONTH)
                   WHEN '4M'  THEN ADDDATE(dt_calc,  INTERVAL 4 MONTH)
                   WHEN '6M'  THEN ADDDATE(dt_calc,  INTERVAL 6 MONTH)
                   WHEN '12M' THEN ADDDATE(dt_calc,  INTERVAL 12 MONTH)
                   WHEN '1W'  THEN ADDDATE(dt_calc,  INTERVAL 1 WEEK)
                   WHEN '2W'  THEN ADDDATE(dt_calc,  INTERVAL 2 WEEK)

            END   INTO  dt_proximo_mes;

         SET va_result = va_result + 1;

         set va_dt_next_mm_calc = DATE_FORMAT(dt_proximo_mes, '%Y-%m-%d');
         SET va_dd_calc            = SUBSTR(va_dt_next_mm_calc, 9, 2);
         SET va_mm_calc            = SUBSTR(va_dt_next_mm_calc, 6, 2);

         if p_fl_frequency not in ('1W', '2W') then
             if va_dd_calc <> va_dd_start then

                if va_mm_calc <> '02' then -- Fevereiro

                   if va_mm_calc in ('04', '06', '09', '11') then  -- Meses com 30 dias
                      set va_dt_next_mm_calc = concat(SUBSTR(va_dt_next_mm_calc, 1, 8),'30');
                   else
                      if va_dd_start = '30' then
                         set va_dt_next_mm_calc = concat(SUBSTR(va_dt_next_mm_calc, 1, 8),'30');
                      else
                         set va_dt_next_mm_calc = concat(SUBSTR(va_dt_next_mm_calc, 1, 8),'31');
                      end if;
                   end if;
                end if;
             end if;
         end if;

            SET dt_calc = STR_TO_DATE(va_dt_next_mm_calc,'%Y-%m-%d');

          if dt_calc >= dt_ends then

             set va_end_loop = 'S';

             if dt_calc > dt_ends then
               SET va_result = va_result - 1;
             end if;

        end if;

    END WHILE;

    -- ------------------------------------------------------------------------------
    -- Return Calc
    -- ------------------------------------------------------------------------------

    select va_result as p_qt_ocorrs;

END

Answer 2

May this code help you

    DECLARE
   first_date   DATE := TO_DATE ('01012015', 'DDMMYYYY');
   last_date    DATE := TO_DATE ('31012015', 'DDMMYYYY');
   a_sequence   NUMBER := 25;
BEGIN
   LOOP
      IF a_sequence = last_date - first_date
      THEN
         EXIT;
      END IF;

      DBMS_OUTPUT.put_line ('Sequence=' || a_sequence);
      a_sequence := a_sequence + 1;
   END LOOP;
END;

Answer 3

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.