4 Replies Latest reply: Jul 30, 2013 7:49 PM by DragonKing RSS

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

    DragonKing

      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

          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

            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

              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

                Thank you Soofi. You're so nice.

                 

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