7 Replies Latest reply: Feb 10, 2014 7:56 AM by 953474 RSS

    How to get second Monday of every month in a given date range?

    953474

      In Oracle forms, how to get the Second Monday of every month in a given date range?

      I've tried below query using WITH Clause, but looks like WITH Clause doesn't work in Oracle forms. So is there any other way to do this in Oracle forms?

       

      WITH   month_range   AS

      (

          SELECT  TO_DATE ('Dec 2013', 'Mon YYYY')  AS first_month

          ,       TO_DATE ('Mar 2014', 'Mon YYYY')  AS last_month

          FROM    dual

      )

      SELECT  NEXT_DAY ( 6 + ADD_MONTHS ( first_month

                                        , LEVEL - 1

                                        )

                       , 'MONDAY'

                       )    AS second_monday

      FROM    month_range

      CONNECT BY  LEVEL <= 1 + MONTHS_BETWEEN (last_month, first_month)

      ;

       

      Thanks in Advance.

        • 1. Re: How to get second Monday of every month in a given date range?
          AnnPricks E

          This is one way

          SELECT second_date,

                 days

          FROM(SELECT startdate+(LEVEL-1) second_date,

                      TO_CHAR((startdate+(LEVEL-1)),'DY') days,

                      ROW_NUMBER() OVER(PARTITION BY TO_CHAR((startdate+(LEVEL-1)),'MM') ORDER BY startdate+(LEVEL-1)) rn

                FROM(SELECT TRUNC(TO_DATE(SYSDATE,'DD-MON-YY'),'MM') startdate,

                            TRUNC(TO_DATE(SYSDATE+300,'DD-MON-YY'),'MM') enddate

                     FROM dual)

                WHERE TO_CHAR((startdate+(LEVEL-1)),'DY') = 'MON'

                CONNECT BY LEVEL <= (enddate-startdate)+1)

          WHERE rn =2;

          • 2. Re: How to get second Monday of every month in a given date range?
            AnnPricks E

            More simplest way

            SELECT NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),(LEVEL-1)),'MON')+7 second_day

            FROM(SELECT SYSDATE startdate,

                        SYSDATE+300 enddate

                 FROM dual)

            CONNECT BY LEVEL <= (MONTHS_BETWEEN(enddate,startdate)+1);

            • 3. Re: How to get second Monday of every month in a given date range?
              953474

              Hi,

               

              Is there a way to get Second Monday of every 2 months or 3 months?

               

              Thanks

              • 4. Re: How to get second Monday of every month in a given date range?
                AnnPricks E


                You can just change the condition like if it is every 2 months then ((LEVEL*2) - 2) 3 means ((LEVEL*3) - 3). And months_between/2 or /3.Try the below and let me know in case of any issues

                 

                ----- Every 2 months

                SELECT CASE WHEN TO_CHAR(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*2)-2)),'DY') = 'MON'

                            THEN NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*2)-2)),'MON')

                       ELSE NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*2)-2)),'MON')+7

                       END AS second_day

                FROM(SELECT SYSDATE startdate,

                            SYSDATE+300 enddate

                     FROM dual)

                CONNECT BY LEVEL <= (MONTHS_BETWEEN(enddate,startdate)/2+1);


                ---- Every 3 months

                SELECT CASE WHEN TO_CHAR(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*3)-3)),'DY') = 'MON'

                            THEN NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*3)-3)),'MON')

                       ELSE NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),((LEVEL*3)-3)),'MON')+7

                       END AS second_day

                FROM(SELECT SYSDATE startdate,

                            SYSDATE+300 enddate

                     FROM dual)

                CONNECT BY LEVEL <= (MONTHS_BETWEEN(enddate,startdate)/3+1);

                 

                • 5. Re: How to get second Monday of every month in a given date range?
                  953474

                  Hi Ann,

                   

                  When I try to run below SQL to get 2nd Thursday of Every Month, I got the results below.  But here 15-MAY-14 really a 3rd Thursday of the Month. Right?

                  Any idea what was wrong in the query?

                   

                  SELECT NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),(LEVEL-1)),'THU')+7 second_day

                  FROM(SELECT SYSDATE startdate,

                              SYSDATE+300 enddate

                       FROM dual)

                  CONNECT BY LEVEL <= (MONTHS_BETWEEN(enddate,startdate)+1);

                   

                  13-FEB-14

                  13-MAR-14

                  10-APR-14

                  15-MAY-14

                  12-JUN-14

                  10-JUL-14

                  14-AUG-14

                  11-SEP-14

                  09-OCT-14

                  13-NOV-14

                   

                  Thanks

                  • 6. Re: How to get second Monday of every month in a given date range?
                    AnnPricks E

                    Good catch, When month's first day is thursday.. So i have changed the query accordingly.. Try the below

                    SELECT CASE WHEN TO_CHAR(ADD_MONTHS(TRUNC(startdate,'MM'),(LEVEL-1)),'DY') = 'THU'

                                THEN NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),(LEVEL-1)),'THU')

                           ELSE NEXT_DAY(ADD_MONTHS(TRUNC(startdate,'MM'),(LEVEL-1)),'THU')+7

                           END AS second_day

                    FROM(SELECT SYSDATE startdate,

                                SYSDATE+300 enddate

                         FROM dual)

                    CONNECT BY LEVEL <= (MONTHS_BETWEEN(enddate,startdate)+1);

                    • 7. Re: How to get second Monday of every month in a given date range?
                      953474

                      Thanks Ann. That's solves my Problem. Thanks for your Help.