Interval between two Dates

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 ?

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.

Calculate Duration Between Two Dates – Results, Or 255 months, 15 days excluding the end date. Alternative time units. 7777 days can be converted to one of these units: 671,932,800 seconds; 11,198,880� Duration Between Two Dates – Calculates number of days. Date Calculator – Add or subtract days, months, years Birthday Calculator – Find when you are 1 billion seconds old

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;

Time Duration Calculator, Time Between Two Dates. Use this time and date duration calculator to find out the number of days, hours, minutes, and seconds between the times on two� To find duration between two dates, we subtract the smaller date from the larger date, using the principle of borrow and re-group used in arithmetic subtraction, in the following order:- Subtract the two day values. If the top value is smaller than the bottom value, borrow the no. of days in the month preceding mm 2 and reduce mm 2 by 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

Days Between Dates & Time Between Dates Calculator, Calculate the days between two dates, as well as hours, with this date calculator tool. Time interval between two dates. Calculate time interval between two dates. person_outlineTimurschedule 2015-08-14 20:39:26. Calculate time interval between two dates

Online calculator: Time interval between two dates, Calculate time interval between two dates. person_outlineTimurschedule5 years ago. Calculate time interval between two dates. Created by users requests. Please apply the below formulas to calculate interval days, weeks, months or years between two given dates in Excel. Calculate interval of days Select a blank cell, enter formula =DATEDIF(A2, B2, "d")into the Formula Bar and press the Enter key. Drag the formula down to apply it to needed cells.

Calculating the Interval Between Two Dates or Times, Calculate time interval between two dates. person_outlineTimurschedule5 years ago. Creative Commons Attribution/Share-Alike License 3.0 (Unported). 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?

How to Find the Interval Between Two Dates in PostgreSQL , Calculating intervals with temporal-difference functions. To calculate an interval in days between two date values, use the DATEDIFF() function: mysql> SET @� Intervals between dates The INTCK function returns the number of time units between two dates. For the time unit, you can choose years, months, The INTNX function returns a SAS date that is a specified number of time units away from a specified date. For example,

Comments