This discussion is archived
4 Replies Latest reply: Jul 30, 2013 5:49 PM by DragonKing RSS

SQL to display start, end date, days in each month between 2 dates

DragonKing Newbie
Currently Being Moderated

HI all,

 

Please hep since I'm so new on this. I have a date range 01/15/2012 to 11/26/2012. What I have to do is to display it like below:

 

01/15/2012, 01/31/2012, 16 days

02/01/2012, 02/29/2012, 29 days

03/01/2012, 03/31/2012, 31 days

.

.

.

11/01/2012, 11/26/2012, 26 days.

 

Thanks for helping me.

  • 1. Re: SQL to display start, end date, days in each month between 2 dates
    Soofi Explorer
    Currently Being Moderated

    Hi King,

     

    Try this Block.....

     

    In SQL---------> SET SERVEROUTPUT ON;

     

     

    DECLARE

       v_from_date    DATE   := TO_DATE ('15-JAN-2012');

       v_to_date      DATE   := TO_DATE ('26-NOV-2012');

       v_cnt          NUMBER;

       v_last_date    DATE;

       v_date         DATE;

    BEGIN

       LOOP

          v_date := v_from_date;

          v_last_date := LAST_DAY (v_from_date);

     

     

          IF v_last_date > v_to_date

          THEN

             v_last_date := v_to_date;

          END IF;

     

     

          v_cnt := (v_last_date - v_from_date) + 1;

          DBMS_OUTPUT.put_line (

             v_date || '  , ' || v_last_date || '  , ' || v_cnt || ' days'

          );

          v_from_date := v_last_date + 1;

          EXIT WHEN v_last_date >= v_to_date;

       END LOOP;

    END;

  • 2. Re: SQL to display start, end date, days in each month between 2 dates
    DragonKing Newbie
    Currently Being Moderated

    THank you, Soofi.

     

    Can you please help me to write in SQL query only?

     

    Regards,

    Harvey

  • 3. Re: SQL to display start, end date, days in each month between 2 dates
    Soofi Explorer
    Currently Being Moderated

    Hi Harvey,

     

    Try this....

     

    SELECT TO_DATE ('15-JAN-2012') start_date,

           LAST_DAY ((TO_DATE ('15-JAN-2012')))

                 end_date,

           (LAST_DAY ((TO_DATE ('15-JAN-2012'))) - TO_DATE ('15-JAN-2012')) no_of_days

      FROM DUAL

    UNION

    SELECT start_date, end_date, no_of_days

      FROM (SELECT     (TO_DATE ('15-JAN-2012') + LEVEL) start_date,

                       LAST_DAY ((TO_DATE ('15-JAN-2012')) + LEVEL)

                             end_date,

                       (  LAST_DAY ((TO_DATE ('15-JAN-2012')) + LEVEL)

                        + 1

                        - (TO_DATE ('15-JAN-2012') + LEVEL)

                       ) no_of_days

                  FROM DUAL

            CONNECT BY LEVEL <= (TO_DATE ('26-NOV-2012') - TO_DATE ('15-JAN-2012')))

    WHERE (   TO_CHAR (start_date, 'DD') = '01'

            OR start_date = TO_DATE ('15-JAN-2012')

            OR start_date = TO_DATE ('26-NOV-2012')

           )

       AND start_date < TRUNC (TO_DATE ('26-NOV-2012'), 'MON')

    UNION

    SELECT TRUNC (TO_DATE ('26-NOV-2012'), 'MON') start_date,

           TO_DATE ('26-NOV-2012')

                 end_date,

           (TO_DATE ('26-NOV-2012') + 1 - TRUNC (TO_DATE ('26-NOV-2012'), 'MON')) no_of_days

      FROM DUAL;

     

     

    I Have hot coded the dates.. You can use parameters as start date and date

    Hope it will work.

     

    Regards,

    Soofi

  • 4. Re: SQL to display start, end date, days in each month between 2 dates
    DragonKing Newbie
    Currently Being Moderated

    Thank you Soofi. You're so nice.

     

    I've tried this sql today. It's very good.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points